I'm having a problem while creating a composite (or compound) key of 3 fields, concatenate using dashes. I use the key as a lookup field to assign user defined codes to a dataset using ApplyMap(). Depending on the order of the operation, the resulting key is calculated incorrectly.
As shown below, if I concatenate the Code1, Code2 and Code3 using dashes, I get some strange, and incorrect, results. When I concatenate Code3, Code2 and Code 1, it gives the correct composite key.
If I change the concatenation to use something other than dashes, it seems to work. Does anyone know why this happens?
I'll attach the qvd and qvw for your testing pleasure.
Marco is right. When creating field values, for performance reasons QlikView will evaluate the pattern of the value and store it as a dual value. Things like xx-xx-xx are recognized as dates, purely on the layout. The actual values may be illegal, but that doesn't matter. A value of 41-09-05 will be stored as a numerical value (the calculated date in days since the epoch) and the first string value that leads to the same numerical value.
This becomes very bizarre when storing keys that look like time values. For example when storing 00:01:00 as a key value, a dual(1/(24*60), '00:01:00') will be stored in the field's internal symbol table. If later on a new key value 00:00:60 is stored in this field, its dual value will not be (1/(24*60), '00:00:60') but (1/(24*60), '00:01:00') because the numerical value was already present in the symbol table. 1 minute (good data) and 60 seconds (bad data) lead to the same numerical time value.
When creating key values, use whatever characters you like except those that are used in the various special formats (dots, commas, hyphens, semicolons and the likes). I always use the vertical bar | as a key character, or I prefix keys with an alphabetic identifier (when I want to know what their source is, like O for orders, I for invoices, Q for quotes etc.)