topic Re: Reg: Loop in the Script in QlikView

Reg: Loop in the Script

lakshmanvepuri — Wed, 25 Nov 2020 16:16:04 GMT

HI Friends,

I had the below Query, How to write in loop. and seperate excel or csv format with Store_StoreName

( StoreNames are Walmart, 7Eleven, Cheers, Wendys, Wimpys, PicknPay etc )

I want data to be stored in separate excel sheets.

StoreData:

Select

Store_Name,

Items,

Quantity

from WareHouse;

STORE StoreData INTO (txt, delimiter is ',')

Drop Table StoreData;

Please advice me on this.

Thanks

Kumar

Re: Reg: Loop in the Script

Anil_Babu_Samineni — Fri, 25 May 2018 06:36:02 GMT

May be try this? I have not test, But technically this would work

StoreData:

Select

Store_Name,

Items,

Quantity

from WareHouse;

Let vTableCount = NoOfRows(‘StoreData’);

For Var = 0 To $(vTableCount)-1

LET vAnil = Peek(‘TableNames’, $(i), ‘StoreData’);

$(vAnil):

SQL SELECT * FROM $(vAnil);

STORE $(vAnil) INTO $(vQVD)$(vAnil).QVD (QVD);

DROP Table $(vAnil);

Next Var

Re: Reg: Loop in the Script

PrashantSangle — Fri, 25 May 2018 09:24:48 GMT

Hi,

You can try with simple for each loop also

For each a in 'Walmart','7Eleven','Cheers','Wendys','Wimpys','PicknPay'

Temp:

LOAD * from

store.xlsx

(ooxml, embedded labels, table is Sheet1)

where Store = '$(a)';

store Temp into $(a).qvd;

DROP Table Temp;

next

Regards,

Re: Reg: Loop in the Script

PrashantSangle — Fri, 25 May 2018 09:48:54 GMT

If you want it in more dynamic way then try below

Temp1:

Load concat(DISTINCT Chr(39) & Store & chr(39),',') as StoreName from

store.xlsx

(ooxml, embedded labels, table is Sheet1);

let vLoopVar = Peek('StoreName',-1,'Temp1');

Drop table Temp1;

For each a in $(vLoopVar)

Temp:

LOAD * from

store.xlsx

(ooxml, embedded labels, table is Sheet1)

where Store = '$(a)';

store Temp into Store_Walmart_$(a).csv (txt, delimiter is ',');

DROP Table Temp;

Regards,