Creating New Calculative Fields in Load Scrip

[saifuddin]

Hi All, 

Part of a bigger project I am working on, I have to figure out numbers of week between  Start and End date. From below screenshot, for each Start_date to End_date I want to figure out weeks in 7 days block. I tried using nested If statement as per below. But, it does not satisfy my need.  In a nutshell I want to create two new fields as per the Desire screenshot below -- Weeks and Dates 

.

Will appreciate your advice.  

Thanks 

Saif

[jyothish8807 · Accepted solution]

Hi Saif,

Try this:

A:
Load
Year,
Period,
Date(Date#(Start_Date,'DD/MM/YYYY'),'DD/MM/YYYY') as Start_Date,
Date(Date#(End_Date,'DD/MM/YYYY'),'DD/MM/YYYY') as End_Date;
LOAD * INLINE [
Year, Period, Start_Date, End_Date
2017, 1, 20/2/2017, 19/05/2017
2017, 2, 19/06/2017, 15/09/2017
2017, 3, 16/10/2017, 19/01/2018
];

MinMax:
Load
min(Start_Date) as MinDate,
max(End_Date) as MaxDate
resident A;

Let vMin=peek('MinDate');
Let vMax=peek('MaxDate');

Drop table MinMax;

Calendar:
LOAD

$(vMin) + Iterno()-1 As Num,

date(weekstart(Date($(vMin) + IterNo() - 1))+1,'DD/MM/YYYY') as Date

AutoGenerate 1 While $(vMin) + IterNo() -1 <= $(vMax);


Map:
left join(A)
IntervalMatch (Date)
Load
Start_Date,
End_Date
Resident A;

Drop table Calendar;

NoConcatenate
Final:
Load
Year,
Period,
Start_Date,
End_Date,
Date,
if(Period <> Previous(Period),1,if(Period = Previous(Period),peek(Week)+1)) as Week
resident A
order by Period,Year;

Drop table A;

[jyothish8807]

[saifuddin]

Thanks KC. Appreciate your help.