Latest day of week

[razvan_brais]

Hello,

Help me with some advice.

I`m loading in my script values for each week. I need to get every week end of every week.

In most cases , saturday is the last day of the week. But there are some cases when I need to get sundays. These cases apear when last day of month is on sunday. Example : 30th september.

So I need to load something like this for example:
Starting from 15 september : saturday

                      22 september : saturday

And the tricky part is here : now I have 29 september - saturday

                                             and 30 september - sunday

                                        6 october - saturday.

In the above example I need to load all info`s and when it comes to 29 and 30 , to load only 30 september and ommit 29 september.

Can you help please?

Hope I made my self clear.

Thank you,

Razvan

[razvan_brais]

This is how I`m loading dates for now:

LOAD

Kpi,

Value,

  WEEK_ID,

HIST_DATE,

Resident Table

Where WeekDay(HIST_DATE) = if(Date(Date#(MonthEnd(HIST_DATE),'M/DD/YYYY') ,'DD-MMM-YYYY') = Date#(HIST_DATE,'M/DD/YYYY') and (WeekDay(Date(HIST_DATE)) = 'Sun'),'Sun','Sat');

But this brings me both : 29 and 30 of september. But I only need just one day of this week.

[jensmunnichs · Accepted solution]

Maybe something like this?

Temp1:

LOAD * Inline [

Date, Value

22-3-2018, 2

23-3-2018, 3

24-3-2018, 1

21-9-2018, 6

22-9-2018, 5

23-9-2018, 8];

Final:

LOAD

    Date,

    Year(Date)&Week(Date) as YearWeek,

    Value

Resident Temp1;

Right Join

LOAD

    Max(Date) as Date

Resident Final

Group by YearWeek;

Drop table Temp1;

Drop field YearWeek;

First load in original data (made something up as I don't have your data to test this), then load from resident but with 'Year(Date)&Week(Date)' to give every week a unique value, then load the max(date) for every YearWeek and right join this to Final, only keeping the records with the max date for that week.

Might have to add some arguments to the Week function to make it fit your needs, see:

https://help.qlik.com/en-US/qlikview/November2017/Subsystems/Client/Content/Scripting/DateAndTimeFun...

This is all assuming you are using a 'full' date including year, if the data will only ever contain 1 year, you don't need Year(Date), just Week(Date), though I'm not sure if the week function will work without a year, so you might have to use MakeDate or something to fix that.

Hope this helps, if you have any questions or if I made a wrong assumption let me know

[razvan_brais]

This is perfect.

Thank you .