Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
arniewolff
Contributor III
Contributor III

Composite Key Creation Error

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

1 Solution

Accepted Solutions
MarcoWedel

Your concatenated key composite1 is treated as date:

QlikCommunity_Thread_147966_Pic1.JPG

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

AutoNumberHash128(Code1, Code2, Code3) as %Key

hope this helps

regards

Marco

View solution in original post

6 Replies
arniewolff
Contributor III
Contributor III
Author

Here's a sample screen shot.Composite Key.JPG

Arnie

jagan
Luminary Alumni
Luminary Alumni

Hi,

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

Regards,

Jagan.

MarcoWedel

Your concatenated key composite1 is treated as date:

QlikCommunity_Thread_147966_Pic1.JPG

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

AutoNumberHash128(Code1, Code2, Code3) as %Key

hope this helps

regards

Marco

Peter_Cammaert
Partner - Champion III
Partner - Champion III

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

MarcoWedel

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

QlikCommunity_Thread_147966_Pic3.JPG

hope this helps

regards

Marco

MarcoWedel

see attachment

regards

Marco