topic Re: Using Row Number and Partition by to sort records in QlikView

Using Row Number and Partition by to sort records

lisab80 — Fri, 18 May 2018 12:20:47 GMT

I am reading in data from a qvd and I would only like to read in the data where the Wait_Date is the closest to the sunday of that current week and ignore all other dates within that same week - as there may be multiple Wait_Date within a week. Week is Mon - Sunday.

Example

Wait_Date

03/05/2018

06/05/2018 - correct date to read in

07/05/2018

09/05/2018

11/05/2018 - correct date to read in

Any help greatly appreciated

Thanks

Re: Using Row Number and Partition by to sort records

olivierrobin — Fri, 18 May 2018 12:43:13 GMT

hello

I would :

1 - load the data and for each day calculate the difference in days between this day and the sunday of its week : store the day, the difference and the sunday

2 - extract for each week the day with the lowest difference (use firsortedvalue)

3 - join the data with this list of days

Re: Using Row Number and Partition by to sort records

YoussefBelloum — Fri, 18 May 2018 13:20:45 GMT

Hi,

here is one solution to do it:

in this example I take the sunday of this week (actual week) as reference

let vSunday=Date(WeekEnd(Today()));

TRACE $(vSunday);

closest_to_sunday_ofactualweek:

LOAD Date('$(vSunday)') as weekend_date, Date#(Wait_Date,'DD/MM/YYYY') as Wait_Date, Interval(Date('$(vSunday)')-Date#(Wait_Date,'DD/MM/YYYY'),'d') as days_diff Inline [

Wait_Date

03/05/2018

15/05/2018

16/05/2018

17/05/2018

11/05/2018

];

join(closest_to_sunday_ofactualweek)

LOAD min(days_diff) as min Resident closest_to_sunday_ofactualweek

Group by weekend_date;

final_table:

NoConcatenate

LOAD * Resident closest_to_sunday_ofactualweek

Where days_diff=min;

DROP Table closest_to_sunday_ofactualweek;

PFA

Re: Using Row Number and Partition by to sort records

marcus_sommer — Fri, 18 May 2018 13:21:32 GMT

I could imagine something like this:

table:

load ..., Wait_Date, week(Wait_Date) as Week, week(Wait_Date) * 10 + weekday(Wait_Date) as WeekFilter

from Source;

inner join(table)

load Week, max(WeekFilter) as MaxWeekDate resident table group by Week;

drop fields Week, WeekFilter, MaxWeekDate;

- Marcus

Re: Using Row Number and Partition by to sort records

lisab80 — Fri, 18 May 2018 14:26:05 GMT

Hi Youssef

I can't use a variable for sunday as I am reading in multiple weeks worth of data so for my original example above I would need the 06/05/2018 and 11/05/2018 extracted as they are the closest date to the sunday of their current week not the current week the program is run. For your example I would extract 03/05/2018, 11/05/2018 and 17/05/2018.

I have modified the code to what I need but I am getting an error:

Field not found - <min>

final_table:

NoConcatenate

LOAD *

Resident temp_table

Where DaysDiff = min

Here is my code

temp_table:
LOAD
CASENOTE,
WAIT_DATE,
Interval(Date(WeekEnd(WAIT_DATE,0,0))-Date#(WAIT_DATE,'DD/MM/YYYY'),'d') AS DaysDiff,
WeekEnd(WAIT_DATE,0,0) as SundayDate

FROM
[..\QVD\WAITING.qvd]
(qvd);

join(temp_table)
LOAD
min(DaysDiff) as min
Group by SundayDate;

final_table:
NoConcatenate
LOAD *
Resident temp_table
Where DaysDiff = min;

drop table temp_table;

Thanks

Re: Using Row Number and Partition by to sort records

lisab80 — Fri, 18 May 2018 14:46:32 GMT

Hi Marcus

The dates in my test example is

22/04/2018

29/04/2018

30/04/2018

06/05/2018

13/05/2018

When I tried your code I am still getting out these 5 dates whereas I should only be getting four

22/04/2018

29/04/2018

06/05/2018

13/05/2018

See code below:

temp_table:
LOAD
CASENOTE,
WAIT_DATE,
week(WAIT_DATE) as Week,
week(WAIT_DATE) * 10 + weekday(WAIT_DATE) as WeekFilter

FROM
[..\QVD\WAITING.qvd]
(qvd);

inner join(temp_table)
load
Week,
max(WeekFilter) as MaxWeekDate
resident temp_table
group by Week;

drop fields Week, WeekFilter, MaxWeekDate;

Thanks

Re: Using Row Number and Partition by to sort records

lisab80 — Fri, 18 May 2018 16:02:46 GMT

Hi Olivier

I can work out the first bit ok but I am struggling with points 2 and 3 would you have an example?

Still very new to qlikview so not very good with its syntax yet

Thanks

Re: Using Row Number and Partition by to sort records

marcus_sommer — Sat, 19 May 2018 09:49:08 GMT

Here with a slight adjustment by using mapping (within an additionally step) instead of joining:

table:

load *, week(Wait_Date) as Week, week(Wait_Date) * 10 + weekday(Wait_Date) as WeekFilter;

load date(date#(Wait_Date, 'DD/MM/YYYY')) as Wait_Date Inline [

Wait_Date

03/05/2018

15/05/2018

16/05/2018

17/05/2018

11/05/2018

];

map:

mapping load Week, max(WeekFilter) as MaxWeekDate resident table group by Week;

final:

noconcatenate load * resident table where applymap('map', Week, 0) = WeekFilter;

drop table table;

- Marcus

Re: Using Row Number and Partition by to sort records

lisab80 — Mon, 21 May 2018 10:51:13 GMT

Hi Marcus

Thank you so much for that reply it works a treat - can I check would this be ok to run over several years - if I am using week as a filter?

Re: Using Row Number and Partition by to sort records

marcus_sommer — Mon, 21 May 2018 11:19:01 GMT

By running over several years the logic needs to be a bit extended maybe in this way:

....

year(Wait_Date) * 100 + week(Wait_Date) as YearWeek

year(Wait_Date) * 1000 + week(Wait_Date) * 10 + weekday(Wait_Date) as YearWeekFilter

...

- Marcus

Re: Using Row Number and Partition by to sort records

lisab80 — Mon, 21 May 2018 16:14:31 GMT

Hi Marcus

That worked perfect - thank you very much for your help - I was really stuck on that problem

Lisa