topic Using preceding load for the having Clause in QlikView

Using preceding load for the having Clause

Anonymous — Wed, 25 May 2016 14:27:57 GMT

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

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 — Wed, 25 May 2016 15:31:26 GMT

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 — Wed, 25 May 2016 15:33:26 GMT

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 — Thu, 26 May 2016 10:45:22 GMT

Thanks for the reply Marcus and Gysbert.

Have followed the solution by Marcus and it worked

- Sonthu