Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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;