Puzzling problem when using left keep

[#1]

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?

[Nicole-Smith · Accepted solution]

The attached solution should work for you.