topic Re: For Loop and saving using field value in App Development

For Loop and saving using field value

bryan_21 — Wed, 26 Oct 2022 08:20:47 GMT

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!

Re: For Loop and saving using field value

rubenmarin — Wed, 26 Oct 2022 09:49:34 GMT

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.

Re: For Loop and saving using field value

bryan_21 — Wed, 26 Oct 2022 09:58:36 GMT

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.

Re: For Loop and saving using field value

rubenmarin — Wed, 26 Oct 2022 10:04:53 GMT

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.

Re: For Loop and saving using field value

Ruhulessin — Wed, 26 Oct 2022 10:11:35 GMT

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 ;

Re: For Loop and saving using field value

bryan_21 — Wed, 26 Oct 2022 10:30:21 GMT

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

Re: For Loop and saving using field value

Ruhulessin — Wed, 26 Oct 2022 11:09:34 GMT

Glad to be of help!

Re: For Loop and saving using field value

bryan_21 — Thu, 27 Oct 2022 03:30:09 GMT

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.