Load data using a loop

[Anonymous]

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.

[hector_munoz]

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:

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

Regards,

H

[Anonymous]

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]

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]

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]

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

[Anonymous]

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]

thank you.