topic Re: Date group by is not working. in QlikView

Date group by is not working.

Vishal — Fri, 05 Aug 2022 12:15:28 GMT

Hello Everyone,

Please refer to below table, wherein I have 'SHIP_DATE' and 'QYT_DISPENSED', and the column Z i.e. 'week_date' I have created using the code as - date(if(WeekDay(SHIP_DATE,0)=4,SHIP_DATE, WeekEnd(SHIP_DATE,0,4)+1)) as week_date - which is basically giving Friday if the date is Friday, otherwise round off the date to the next Friday.

Now, I need to replicate the below SAS code into Qlikview -

I have used the below code -

However, the result is not as I wanted. It may seem trivial to some but I am really stuck here. Kindly help. The result I am getting is -

It is supposed to show 04-22-16 = 16 or 04-15-16 = 6. It is not grouping the dates as its supposed to. The problem I have figured is that it is grouping the 'QYT_DISPENSED' of actual Fridays separately than the dates I have converted to Friday. See below -

Please help! Thanks in advance.

Re: Date group by is not working.

BrunPierre — Fri, 05 Aug 2022 14:16:44 GMT

Maybe this;

[Temp_interim.nps_starter_pack];
NoConcatenate
LOAD DATE(DATE#(week_date,'DD-MM-YYYY'),'MM-DD-YYYY') as weekdate,
QTY_DISPENSED as NPS,
'SD_starter_pack_NPS' as Flag

resident interim.SD_final;
Where wildmatch(NDC_NBR,'000123456') and not wildmatch(ACCOUNT_NAME,'*BLOCKED*');

NoConcatenate
interim.nps_starter_pack:
LOAD weekdate,
Flag,
SUM(NPS) as NPS,

resident [Temp_interim.nps_starter_pack]
Group by weekdate,Flag;
Order by weekdate,Flag;
Drop table [Temp_interim.nps_starter_pack];

Re: Date group by is not working.

Vishal — Fri, 05 Aug 2022 15:34:45 GMT

Thanks @BrunPierre , It worked!

Some syntax here and there, and Group by and Order by would not work together. But, I got the idea. Thanks again!