Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Drop Table StoreData;
Please advice me on this.
Thanks
Kumar
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
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,
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;
next
Regards,