topic Composite Key Creation Error in QlikView

Composite Key Creation Error

arniewolff — Sat, 10 Jan 2015 02:12:50 GMT

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.

Thanks,

Arnie

Re: Composite Key Creation Error

arniewolff — Sat, 10 Jan 2015 02:14:37 GMT

Here's a sample screen shot.

Arnie

Re: Composite Key Creation Error

jagan — Sat, 10 Jan 2015 04:41:38 GMT

Hi,

The values are concatenated correctly right? What is your expected output?

Regards,

Jagan.

Re: Composite Key Creation Error

MarcoWedel — Sat, 10 Jan 2015 10:47:34 GMT

Your concatenated key composite1 is treated as date:

Another possibility to create a key out of those 3 fields would be:

AutoNumberHash128(Code1, Code2, Code3) as %Key

hope this helps

regards

Marco

Re: Composite Key Creation Error

Peter_Cammaert — Sat, 10 Jan 2015 12:38:31 GMT

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.)

Good luck,

Peter

Re: Composite Key Creation Error

MarcoWedel — Sun, 11 Jan 2015 01:05:13 GMT

Another solution could be to load explicitly as text:

load
      Text(Code1&'-'&Code2&'-'&Code3) as Composite1,
      Text(Code3&'-'&Code2&'-'&Code1) as Composite2,
      Code1,
      Code2,
      Code3
FROM http://community.qlik.com/servlet/JiveServlet/download/692610-144572/UserCodes.qvd (qvd);

hope this helps

regards

Marco

Re: Composite Key Creation Error

MarcoWedel — Sun, 11 Jan 2015 01:07:00 GMT

see attachment

regards

Marco