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