Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Live chat with experts, bring your API Integration questions. June 15th, 10 AM ET. REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
sonthu02
Contributor III
Contributor III

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
marcus_sommer
MVP & Luminary
MVP & Luminary

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

     ;


talk is cheap, supply exceeds demand
sonthu02
Contributor III
Contributor III
Author

Thanks for the reply Marcus and  Gysbert.

Have followed the solution by Marcus and it worked 

- Sonthu