topic Re: Need help with AGGR in QlikView

Need help with AGGR

Anonymous — Mon, 21 Nov 2016 14:45:00 GMT

Hello,

I have the below AGGR function in a button that selects all the visits wherever the KeyDate is null. However, I need to edit little bit so that the function can select all the visits where the KeyDate is null and Balance is not equal to zero.

=Aggr(if(isnull(KeyDate),VisitNumber),VisitNumber)

Could someone please help me modify to include the balance field.

Thanks much.

Re: Need help with AGGR

sunny_talwar — Mon, 21 Nov 2016 14:54:01 GMT

May be this:

=Aggr(if(isnull(KeyDate) and Sum(Balance) <> 0, VisitNumber), VisitNumber)

Re: Need help with AGGR

Anonymous — Mon, 21 Nov 2016 15:17:14 GMT

Hey Sunny,

Sorry, I am little confused. Clicking the button with AGGR is not selecting anything. How about a listbox expression to perform this? I am trying =if(IsNull(KeyDate),'No KeyDate','VisitsWithKeyDate'). I need to select V0004 and V0005 in below example.

VisitNumber	KeyDate	Bal
V0001	1/23/1985	100
V0002	2/11/1985	200
V0003	5/9/1986	350
V0004		400
V0005		500

Re: Need help with AGGR

sunny_talwar — Mon, 21 Nov 2016 15:51:41 GMT

May be like this

Field

VisitNumber

Search String

='=Sum({<VisitNumber = {"=Len(Trim(KeyDate)) = 0"}>}Bal) > 0'

Re: Need help with AGGR

Anonymous — Mon, 21 Nov 2016 16:16:17 GMT

My QlikView server is frozen. I am rebooting the server. I will try your expression in few mins.

Re: Need help with AGGR

Anonymous — Mon, 21 Nov 2016 16:26:36 GMT

Wow Sunny! man ='=Sum({<VisitNumber = {"=Len(Trim(KeyDate)) = 0"}>}Bal) > 0' worked. Please do me a last favor. How about if we ignore the balance field now? How can I get all the visits that has null KeyDate regardless of balance.

Re: Need help with AGGR

sunny_talwar — Mon, 21 Nov 2016 16:29:37 GMT

May be try removing > 0 at the end and see if that works

Field

VisitNumber

Search String

='=Sum({<VisitNumber = {"=Len(Trim(KeyDate)) = 0"}>}Bal)'

Re: Need help with AGGR

sunny_talwar — Mon, 21 Nov 2016 16:31:31 GMT

or may be this:

Field

VisitNumber

Search String

Concat(DISTINCT {<VisitNumber = {"=Len(Trim(KeyDate)) = 0"}>} Chr(34) & VisitNumber & Chr(34), '|')

Re: Need help with AGGR

Anonymous — Mon, 21 Nov 2016 16:38:39 GMT

The following did not work:

Concat(DISTINCT {<AccountNumber = {"=Len(Trim(KeyDate)) = 0"}>} Chr(34) & AccountNumber & Chr(34), '|')

Re: Need help with AGGR

sunny_talwar — Mon, 21 Nov 2016 16:39:38 GMT

My bad, forgot to add parenthesis. Try this:

='(' & Concat(DISTINCT {<VisitNumber = {"=Len(Trim(KeyDate)) = 0"}>} Chr(34) &VisitNumber & Chr(34), '|') & ')'

Re: Need help with AGGR

Anonymous — Mon, 21 Nov 2016 16:43:57 GMT

You are super genius in QlikView Sunny. I owe a lunch some day! Thanks so much for all your help all the time.