Converting to text not always working
Found a possible bug (feature:) in actual QV 8.5 when i was trying to build an key field for YearMonth.
In my first try Year & ‘-’ & Month was interpreted as a mathematical minus and thus calculated incorrectly. To convert to string explicitly i tried:
text(Year & ‘-’ & Month) as YYYYMM,
which results in NULL for every key generated.
This occurs at least on QlikView 8.5 Enterprice on a 2003 Server 64 Bit.
So don’t use that.
Qonnections 2009
Tommorow we’ll fly to Barcelona to attend to this years Qonnections in Barcelona. I’m looking forward to some interesting technical tracks according QlikView and meeting you people.
Turn Caps Lock Key into Shift Key
without a hazzle.
The following article shows on how to change the behaviour of the caps lock key into a simple Shift Key.
For Windows only as far as i read.
Converting types from left to right
An interesting behaviour showed up last week in a script, that wasn’t doing what it should during load.
It should’ve built some new key fields with translated time values eg.
the key:
company & '-' & type & '-' & %year & '-' & %week as %key,
value
looks like:
evaco-ec-2009-10
1000
qliktech-ec-2009-9
500
and so on.
Now i would like to get this value a week before, for some more complicated reason set analysis isn’t possible.
so i manually built the key one week earlier, where value would be seen as our field forecast
company & '-' & type & '-' &
if(&week = 1, %year-1 & ‘-’ & 52,
%year & ‘-’ & %week - 1),
value as forecast
running this load script showed a lot of keys containg only
company & ‘-’ & type & ‘-’
and nothing afterwards. After some tryouts, thinking i came to the conclusion, that qlikview somehow interprets the string from left to right doing a lot of handy conversions on the fly, as we are building a large string as new key, we concatenate each part. And going from left to right qlikview seems to give strings as higher priority as mathematical operation minus.so it gets something like 2009 as a string and tries to subtract 1 from it this obviously gets null() as result and the string would be shorter than.
A correct result delivers the explicit brackets around that expression:
company & '-' & type & '-' &
if(&week = 1, (%year-1) & ‘-’ & 52,
%year & ‘-’ & (%week-1) ),
value as forecast
the behaviour expected was at least an error or warning during load, not silent dropping of an obvious calculation error. But sure null() is one total correct result for subtractiong a value from a string.
Vista behaviour by design
After running out of battery (again) by vista not playing the low/critical battery alarms i searched the web finding enlightenment in ms kb:
Symptoms
Consider the following scenario. When your computer’s battery reaches low or critical levels, you only receive a pop-up balloon notification, and do not hear a sound file play.
You right-click on your desktop and click Personalize, then choose Sounds. You observe that “Low Battery Alarm” and/or “Critical Battery Alarm” are configured to play a sound file.
Cause
This behavior is by design.
The “Low Battery Alarm” and “Critical Battery Alarm” options in “Sounds” are not used by the battery meter in Windows Vista. These options exist for third-party battery meter applications.
need more?
the behaviour of nicely arranged desktop icons always falling apart, when changing screen resolutions, is also by design since Windows 95.
Microsoft(tm) by designing since 25 years.
Just my 2 cent.
Interesting approach on putting qvw into subversion
Everybody who has ever developed QlikView documents in a team has met several restrictions, like being a binary only format is not the “ideal” format to be put in a text (and source code) oriented versioning system. But every QlikView document has source code: the load script, macro, diagram formulas, variable contents and format information.
There are some ways on extracting parts of these information into xml Files to a subdirectory, which than can be stored in subversion. Read the rest of this entry »
Das Internet in Buchform
Beim Surfen durchs Internet ist mir ein wirklich wunderschöner Kurzfilm über den Browser gelaufen.
Itunes Hotkeys
Für das Problem, dass unter der Windows Version von Itunes nur dann die Hotkeys zum Abspielen und insbesondere Pausieren von Liedern funktionieren, kann mensch mit dem praktischen Programm Itunes Hotkeys beheben. Damit lassen sich dann Globale Hotkeys zum Abspielen/pausieren und auch noch zum bewerten von Liedern einstellen.
Praktisch.
Eine weitere Möglichkeit wäre es mit AutoHotKeys zu realisieren, dazu muss man allerdings vorher das Itunes SDK herunterladen, was den berühmten Kanonen auf die Spatzen entsprechen würde.
Die besten Einparkfehler
Mal so kommentarlos :->
Leading Zero in Time Field
Having troubles converting non standard time stamps into valid time stamps. I’m using a concatenated Date and Time as a sort criteria in getting an actual latest transaction time in a stock.
Hence I’m converting the non standard time stamp of the database into a qlikview timestamp, which i defined as:
SET TimeFormat='hhmmss';
SET DateFormat='YYYYMMDD';
SET TimestampFormat='YYYYMMDD hhmmss[.fff]‘;
the problem occurs, that the time stamp is delivered formatted as strings (hhmmss) which contain no leading zeros. eg:
94300
104400
235959
Sorting this leads to wrong sort order. converting this to timestamp delivers breathtaking values. So i preformatted the time
like this:
time(time#(num(TransactionTime,'000000'),'hhmmss'),'hh:mm:ss') as TransactionTime,
and it delivers the correct result:
094300
104400
Just wondering why in other ETL programs - or even SQL one has a formatting string for solving this case. The above workaround functions,
but i got the feeling, that i’m missing something here?!