Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

arniewolff
New 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

Re: Composite Key Creation Error

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

6 Replies
arniewolff
New Contributor III

Re: Composite Key Creation Error

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

Arnie

MVP
MVP

Re: Composite Key Creation Error

Hi,

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

Regards,

Jagan.

Re: Composite Key Creation Error

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

Re: Composite Key Creation Error

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

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

Re: Composite Key Creation Error

see attachment

regards

Marco

Community Browser