For Loop and saving using field value

[bryan_21]

Hello, I have a field name SHOPCODE and SHOPNAME. When I try to use Load distinct SHOPCODE, there will be 40 values. 

How can I get the name of those 40 unique values and save it using for loop with the name of the unique value?

Like this:

SHOPTABLE:

for NumberOFShop = SHOPCODE

Load SHOPNAME

STORE * FROM SHOPTABLE INTO [lib://SHOPCODE.QVD];

The SHOPCODE.QVD should be the name of the value from the SHOPCODE field. 

Thank you!

[rubenmarin]

Hi, i don't think you need the loop, just do:

LOAD distinct SHOPNAME From/resident...

Another option could be

LOAD SHOPNAME From/resident... where not Exists(SHOPNAME);

So you have a table with the diffrent values, and you can store it to qvd.

[bryan_21]

I want to get the value of the field SHOPCODE so that all of those will be named after the value.

Sample: 

SHOPCODE, SHOPNAME

1, aa

2, bb

3, cc

The file name of the qvd will be the value from the SHOPCODE therefore based on the sample there will be 3 QVD to be save with data from the SHOPNAME field. Thats what I would like to happen.

[rubenmarin]

Hi, then after you have the table ith all distint values you can iterate using:

FOR i=0 to NoOfRows('TableName')-1
  LET vShopCode=Peek('SHOPCODE',$(i),'TableName');
  ...
NEXT

In each iteration of the bucle, the variable vShopCode will have the code of the shop, so you use it to name the qvd or as a filter in querys.

[Ruhulessin · Accepted solution]

Hi @bryan_21,

You can try the following.

Shop:
LOAD
     SHOPCODE
     ,SHOPNAME
     ,ADDITIONAL_FIELDS_IF_NEEDED
FROM SOURCE ...
;

FOR EACH i IN FieldValueList('SHOPNAME')
SET vShopName = '$(i)';

NOCONCATENATE
[$(vShopName)]:
LOAD *
RESIDENT Shop
WHERE SHOPNAME = '$(vShopName)'
;
STORE $(vShopName) INTO [lib://SHOP/$(vShopName).QVD]
;
DROP TABLE $(vShopName)
;

NEXT i

DROP TABLE Shop
;

[bryan_21]

Thank you @Ruhulessin  and @rubenmarin ! Glad you've replied to my query.

[Ruhulessin]

Glad to be of help!

[bryan_21]

Hello @rubenmarin  @Ruhulessin  

I just have additional query that I hope you can be of help.

Now I have additional fields that I want to add aside from the SHOPCODE and SHOPNAME which is the PRODUCTCODE and QTY. How do you sum the values of QTY with same PRODUCTCODE? 

The fields will be SHOPCODE, SHOPNAME, PRODUCTCODE and SALESQTY.