topic Re: Loop based on field in QlikView

Loop based on field

Vengatesh — Sat, 16 Nov 2024 03:26:22 GMT

Hi All,

I need to create new records based on fields. I have 4 columns Ord No, Freq, Amount,Due Date. Now I want to create a new record based on Freq and Due Date.

Please refer attached excel.

For Example:

If Freq is 4 and Date is 15/Jan/19. I want to insert new records with increment of 4 months up to Next Year end (31/Dec/20). so the new records should be 15/May/19, 15/Sep/19, 15/Jan/20.... 15/Sep/20.

Re: Loop based on field

avinashelite — Wed, 29 May 2019 09:31:30 GMT

Basically you want to duplicate the records for specific record combination . How about the other column data ? it should be duplicated ?

Easy way would be to create an resident load for the required records and create an calendar according to your requirement and finally join and link those table

https://community.qlik.com/t5/QlikView-Scripting/Creating-A-Master-Calendar/td-p/341286

Re: Loop based on field

Vengatesh — Wed, 29 May 2019 09:39:36 GMT

Thanks for the quick response,
However we already have the Master Calendar. I don't see how to create resident load for required records. And For your first question Yes, other than Due Date column everything else should be duplicated.

Re: Loop based on field

avinashelite — Wed, 29 May 2019 09:52:32 GMT

1.First create an calendar for the required date range

2. Identify the records that needs to be duplicate with an flag like 1 and 0

3. Now in your calendar hard code the flag field with 1 with the same flag name

4. do an left join with the flag...Done , you will get the duplicate records for the records records with additional date

Note . Date/Month field name should be same while joining

Re: Loop based on field

Vengatesh — Wed, 29 May 2019 10:09:31 GMT

I have a master calendar 2016-2020

Every records needs to be duplicated based on Frequency Field.

I don't want to create records with unwanted dates.

For example in attached xl

For Order No- 2456 only FOUR duplicated records with new Dates should be there.

Re: Loop based on field

avinashelite — Thu, 30 May 2019 07:10:29 GMT

LOAD [Ord No],
Frequency,
Amount,
[Due Date],
[Due Date] as [Future Date]
FROM
[C:\Users\avinash5\Downloads\Loop.xlsx]
(ooxml, embedded labels, header is 1 lines, table is Sheet1);
Concatenate

LOAD *,
IterNo() as Test,
AddMonths([Due Date] ,(Frequency+ (Frequency*IterNo()))) as [Future Date],
Date(MakeDate(2020,12,31),'DD/MM/YYYY') as test_date
While AddMonths([Due Date] ,(Frequency*IterNo())) <= Date(MakeDate(2020,12,31),'DD/MM/YYYY');

LOAD [Ord No],
Frequency,
Amount,
[Due Date]
FROM
[C:\Users\avinash5\Downloads\Loop.xlsx]
(ooxml, embedded labels, header is 1 lines, table is Sheet1);

Got your issue now , please find the attachment for the solution

Don't forget to like and mark as solution

Re: Loop based on field

Vengatesh — Fri, 31 May 2019 06:54:54 GMT

Excellent, This is what i was looking for. Thank you.

I made little changes. But overall this is great. Didn't know preceeding load can work in this way.

Sorry for the Delay.

Re: Loop based on field

avinashelite — Fri, 31 May 2019 07:05:05 GMT

Aha yup , preceding load and while loop did the trick !!

Happy Qlik!!