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:
date(max(effdat)) as effdat
GROUP BY membno