Reg: Loop in the Script

[lakshmanvepuri]

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

[Anil_Babu_Samineni]

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

[PrashantSangle]

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,

[PrashantSangle]

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,