Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
After searching a lot for the having clause in the Qlikview SQL script, Realized that it does not exist in qlikview. Am i right?
After some more thorough research found that we need to use preceding load for the above process.
So, I tried the same in the below query.
MEDM:
EFFDAT is in the form yyyy.mm.dd
membno | effdat |
---|---|
2345668 | 2016.01.05 |
2345668 | 2016.01.07 |
2345668 | 2016.01.08 |
2456789 | 2016.01.06 |
MEDM is a resident table created above
medm1:
load * where effdat = maxeffdat;
load membno, effdat, max(effdat) as maxeffdat resident medm group by membno;
but dilemma is that it does not work , as i want a single EFFDAT(i.e MAXIMUM value) for a single MEMBNO
result should be as follows
membno | effdat |
---|---|
2345668 | 2016.01.08 |
2456789 | 2016.01.06 |
Please let me know if i am doing something wrong or some more enhancement is required in the query.
Thanks in advance
Regards,
Sonthu
Maybe something like this could work (you need to ensure that effdat is numeric):
load membno, effdat resident medm;
inner join
load membno, max(effdat) as effdat resident medm group by membno;
- Marcus
After searching a lot for the having clause in the Qlikview SQL script, Realized that it does not exist in qlikview. Am i right?
Yes, that's correct. But in Qlikview you can solve that with a preceding load.
but dilemma is that it does not work , as i want a single EFFDAT(i.e MAXIMUM value) for a single MEMBNO
result should be as follows
That's because you include effdat in the group by. That way the max(effdat) is calculated at the membno-effdat level instead of the membno level.
Try this instead:
LOAD
membno,
date(max(effdat)) as effdat
RESIDENT
MEDM
GROUP BY membno
;
Thanks for the reply Marcus and Gysbert.
Have followed the solution by Marcus and it worked
- Sonthu