Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

sonthu02
New Contributor II

Using preceding load for the having Clause

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

membnoeffdat
23456682016.01.05
23456682016.01.07
23456682016.01.08
24567892016.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

membnoeffdat
23456682016.01.08
24567892016.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

3 Replies

Re: Using preceding load for the having Clause

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

Re: Using preceding load for the having Clause

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

     ;


talk is cheap, supply exceeds demand
sonthu02
New Contributor II

Re: Using preceding load for the having Clause

Thanks for the reply Marcus and  Gysbert.

Have followed the solution by Marcus and it worked 

- Sonthu

Community Browser