SQL equivalent of WHERE ID IN ('..', '..') in qlikview load

[#1]

Hi,

I have data already loaded (from SQL) in Qlikview. I have another tablel table (excel) that contains the list whose data needs to be updated

AllData:

LOAD *;

Select a, b, c from tab1;

UpdateList:

Load updateList from tab2.xls

Now lets say list to be updated = ('val1','val2','val3')

I want to write query something like below in Qlikview:

UPDATE tab1 set c=1 where b in ('val1','val2','val3')

UPDATE tab1 set c=0 where b not in ('val1','val2','val3')

What is Qlikview equivalent query for above? Basically I want to update column of an already loaded table based on the list provided in other table.

Thanks.

[prieper]

Am not that firm in SQL, WILDMATCH-function corresponds to the IN-command in SQL:

LOAD * FROM .... WHERE WILDMATCH(YourField, ''val1','val2','val3');

If referring to previously load values, you may also use the EXISTS-function.

HTH
Peter

[#3 · Accepted solution]

Alter your script as follows:

AllData:

LOAD *;

Select a, b, c as original_c from tab1;

//UpdateList:

left join (AllData)

Load updateList as b, 1 as new_c from tab2.xls

UpdatedAllData:

noconcatanate load a,b, if(new_c=1,new_c,original_c) as c resident AllData;

drop table AllData;

My assumtion is you want to keep the original value of c if the data is not in updatelist, if this is not a correct assumption replace the field in bold with '0', this is not so much an update as a "flag on load" but the dropping of the original table makes it appear as an update statement

p.s. i didnt try this on a live system so there may be typos/syntax nuances, especially around the spelling of concatenate 😉

[#4]

You can try the function Match

LOAD * FROM TABLE.QVD (qvd) WHERE match(CODE, 'a', 'b', 'f');

[#5]

Perfect stuff!

This is what I was looking for 🙂

Thanks SQL-Cowboy.