Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have Year Field with Years, Week Day field with what day of the week it is and Week Number field with Week numbers. I need to create a field that shows the week number and the starting date of each week. My week starts on Monday and ends on Sunday. How to achieve this?
Below is the sample data
Thank you
Maybe the below one?
Not sure if correct, I did 2 preceding load.... Maybe there is a better way to do it?
Since you've no dates, I did preceding load on original table as
WeekDay(MakeWeekDate(Year,Week,0)) as WD,
After this I did one more preceding load in same table
MakeWeekDate(Year,WD,0) as WeekDate1;
In script it would be
Calendar:
Load*,
MakeWeekDate(Year,WD,0) as WeekDate1; //////Second Preceding Load
Load*,
WeekDay(MakeWeekDate(Year,Week,0)) as WD; //////First Preceding Load
Load
.........
....
From....;
But this is only for 2017 year. If you want to have more years then when you select Week 1 it would show Monday for all the years.
For multiple years, you have to select a Year first and then week.
I am glad you figure it out.
Just to be clear, my version solution does not required to previously create dates in Excel.
The parameters in the MakeWeekDate function are taking from previously loaded date from any source.
he table on the left is from the result in QlikView not from Excel.