Loop based on field

[Vengatesh]

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.

[avinashelite]

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

[Vengatesh]

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.

[avinashelite]

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 

[Vengatesh]

 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.

[avinashelite · Accepted solution]

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 

[Vengatesh]

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.

[avinashelite]

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

Happy Qlik!!