Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Highlighted
Partner
Partner

Loop based on field

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.

You Know What To Do.
Tags (4)
Labels (1)
1 Solution

Accepted Solutions

Re: Loop based on field

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 

7 Replies

Re: Loop based on field

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

Partner
Partner

Re: Loop based on field

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.
You Know What To Do.

Re: Loop based on field

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 

Partner
Partner

Re: Loop based on field

 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.

 

 

You Know What To Do.

Re: Loop based on field

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 

Partner
Partner

Re: Loop based on field

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.

You Know What To Do.

Re: Loop based on field

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

 

Happy Qlik!!