6 Replies Latest reply: Jan 10, 2015 8:07 PM by Marco Wedel RSS

    Composite Key Creation Error

    Arnie Wolff

      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
          Arnie Wolff

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

          Arnie

          • Re: Composite Key Creation Error
            Marco Wedel

            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
                Peter Cammaert

                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
                Marco Wedel

                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