1 Reply Latest reply: May 2, 2012 3:08 PM by Nicole Smith RSS

    Puzzling problem when using left keep

      Hello,

       

      I have the following script:

       

      ItemData:
      LOAD * INLINE [
          ITEMID, ITEMNAME
          001, Item X
          002, Item Y
          003, Item Z
          004, Item A
          005, Item B
          006, Item C
      ];

       

      ItemPrices:
      Left Keep LOAD * INLINE [
          ITEMID, ITEMPRICE
          1, 21
          2, 54
          3, 8
          4, 9
          5, 86
          6, 12
          001, 211
          002, 542
          003, 83
          004, 94
          005, 862
          006, 123
      ];

       

      I would expect from this that all ItemPrices with ITEMID 1 through 6 would be removed while 001 through 006 would be kept. Unfortunately it seems QlikView joins on the numeric value and that causes problems.

       

      Trying to get the price for Item A results in both "9" and "94".

       

      I tried adding 'x' to the ITEMID to force qlikview into recognizing all ID's as strings, by appending the following script:

      ItemData2:
      LOAD 'x' & ITEMID as ITEMID2, ITEMNAME
      Resident ItemData;

      drop table ItemData;

       

      ItemPrices2:
      LOAD ITEMPRICE as ITEMPRICE2, 'x' & ITEMID as ITEMID2
      Resident ItemPrices;

      drop table ItemPrices;

       

      The result is the same, and when I goto preview and look at the values found in ItemPrices2, there is x001 TWICE! How that happens is puzzling to me. I would expect one x1 and one x001... I figured that ItemData and ItemPrice may have been prejoined and then relocated ItemData2 to a location right after ItemData.

      Now that produces x1 TWICE and no x001.... Very odd to me. Seems like QlikView interprets 'x' & ITEMID as ITEMID2 as 'x' & abs(ITEMID) as ITEMID2.

       

      What should I do to force QV to join on string values?