Using preceding load for the having Clause

[Anonymous]

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

[marcus_sommer]

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

[Gysbert_Wassenaar]

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

     ;

[Anonymous]

Thanks for the reply Marcus and  Gysbert.

Have followed the solution by Marcus and it worked 

- Sonthu