Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Load data using a loop

I'm not sure if I'm asking this correctly, but this is essentially what I want to do in a nutshell.

I have data that I'm loading that has a begin date and an end date.  so, I want to duplicate the data so that I can chart it by month and by year.  for example...

jane doe starts employment march 1, 2017 and ends employment August 31 2017.  I want to chart everyone that it's employed for the next 12 months.  so, for feb, she would be a no.  for march she would be a yes, for april she would be a yes, and so forth..

the data currently looks like this.

Name                   Start Date                 End Date

Jane Doe             3/1/2017                    8/31/2017

John Doe              1/1/2017                   1/1/2020

and I want it to look like this ...

Name                   Start Date                 End Date             MonthStatus                Month                               

Jane Doe             3/1/2017                    8/31/2017             0                                  Feb                   

Jane Doe             3/1/2017                    8/31/2017             1                                  March        

Jane Doe             3/1/2017                    8/31/2017             1                                  April

...                       

John Doe            1/1/2017                   1/1/2020                 1                                  Feb

John Doe            1/1/2017                   1/1/2020                 1                                  March

I will be updating the data monthly, so in the load, I would have a variable for current month and I would want to load current month + 11...

I also want to do the same routine for Fiscal year, but I'm sure it'll be the same code...except instead of MonthStatus, it would be YearStatus and Month would be FY.

7 Replies
hector_munoz
Specialist
Specialist

Hi Maria,

You could create a cartesian product by making a JOIN against all the employees in a subset (start date in present month and the following 11) with 12 values of months:

24-02-2017 16-14-00.png

I attach you a sample, hope it serves...

Regards,

H

Anonymous
Not applicable
Author

hi.  thank you for the response.

can you put the script directly  into the message?  I cannot download and view qvf files due to network restrictions..

hector_munoz
Specialist
Specialist

Yes:

T2:

LOAD * INLINE [

Name2, Start Date2, End Date2

Jane Doe, 3/1/2017, 8/31/2017

John Doe, 1/1/2017, 1/1/2020];

LET vsMonthStart = 201701;

LEFT JOIN (T2)

LOAD *,

  Month(Month_Num2) AS Month2;

LOAD Date(MakeDate(Year(AddMonths(Date#('$(vsMonthStart)', 'YYYYMM'), Recno() - 1)),

              Month(AddMonths(Date#('$(vsMonthStart)', 'YYYYMM'), Recno() - 1)),

              1), 'M/D/YYYY') AS Month_Num2

AUTOGENERATE 12;

T1:

LOAD Name2 AS Name,

  [Start Date2] AS [Start Date],

  [End Date2] AS [End Date],

  Month_Num2 AS Month_Num,

  Month2 AS Month

RESIDENT T2

WHERE Date#([Start Date2], 'M/D/YYYY') <= Date#(Month_Num2, 'M/D/YYYY');

DROP TABLE T2;

Regards,
H

hic
Former Employee
Former Employee

Try something like

People:
Load
Name,
Date#([Start Date],'M/D/YYYY') as [Start Date],
Date#([End Date],'M/D/YYYY') as [End Date]
Inline
[Name,Start Date,End Date
Jane Doe,3/1/2017,8/31/2017
John Doe,1/1/2017,1/1/2020]
;

People_x_Months:
Load
Name,
AddMonths(MonthStart([Start Date]),IterNo()-1) as YearMonth
Resident People
While AddMonths(MonthStart([Start Date]),IterNo()-1) <= [End Date];

Then you will get all months between the start and end dates in the field YearMonth. See also Creating Reference Dates for Intervals

HIC

Anonymous
Not applicable
Author

Thank you, all.  I will give this a try...  I'm sure i'll be back though! 

Anonymous
Not applicable
Author

so, if I'm using as a dimension, I don't use aggr, but if I'm trying to calculate a measure, then I use aggr? 

Anonymous
Not applicable
Author

thank you.