I have the following script:
LOAD * INLINE [
001, Item X
002, Item Y
003, Item Z
004, Item A
005, Item B
006, Item C
Left Keep LOAD * INLINE [
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:
LOAD 'x' & ITEMID as ITEMID2, ITEMNAME
drop table ItemData;
LOAD ITEMPRICE as ITEMPRICE2, 'x' & ITEMID as ITEMID2
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?