topic Re: Identify Pure/Mixed Expression in QlikView

Identify Pure/Mixed Expression

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Need help please, sorry if I'm not following all protocols, it's my first post.

I'm trying to compare INVOICE# with SUB and determine in TYPE (new field), when an Invoice# contains more than one Sub; If the same code appears in Sub for that particular Invoice# (which may repeat), that makes it Pure, if varying codes under Sub for one Invoice#, then Mixed.

In EXCEL I use the following Formula:

=IF(COUNTIFS(A$2:A$13,A2,B$2:B$13,B2)=COUNTIF(A$2:A$13,A2),"Pure","Mixed")

The QlikView Developer II book shows the AGGR Function

aggr(count(Distinct OrderID), Customer ID)

Should I be looking at a similar expression?

Invoice#	Sub	Type
23359691	AGR	Pure
23359691	AGR	Pure
23359691	AGR	Pure
23359691	AGR	Pure
23359692	AGR	Mixed
23359692	AGR	Mixed
23359692	AGR	Mixed
23359692	OIF	Mixed
23359693	AGR	Pure
23359693	AGR	Pure
23359693	AGR	Pure
23359693	AGR	Pure

Re: Identify Pure/Mixed Expression

sunny_talwar — Mon, 08 Jan 2018 13:34:56 GMT

Try this

=If(Count(TOTAL <Invoice#> Invoice#) = Count(TOTAL <Invoice#, Sub> Invoice#), 'Pure', 'Mixed')

Re: Identify Pure/Mixed Expression

Anonymous — Thu, 08 Feb 2018 22:21:17 GMT

Hi Sunny, thank you so much for your reply. QlikView seems to have an issue with the formula:

=if(Count(TOTAL <[Invoice Number]> [Invoice Number]) = Count(TOTAL <[Invoice Number], [Sub Section]> [Invoice Number]), 'Pure', 'Mixed')

I'm sure it's the same formula, but it didn't work for me, I removed the brackets to make it just like what you sent, but still not working, all records show as "Pure", even when the repeated Invoice has different Subs in the data.

Invoice Number	Sub Section	Pure/Mix

48450188	NSP	Pure
48450189	OIF	Pure
48450190	AED	Pure
48450190	NSP	Pure
48450190	NSP	Pure
48450190	NSP	Pure
48450196	AGR	Pure
48450197	AED	Pure
48450198	NSP	Pure
48450199	AED	Pure
48450200	AED	Pure
48450202	AGR	Pure
48450202	AGR	Pure
48450203	AED	Pure
48450203	AGR	Pure
48450204	AAS	Pure
48450204	STA	Pure

Anything I might be doing wrong?

Thanks again for your help.

Re: Identify Pure/Mixed Expression

sunny_talwar — Sun, 11 Feb 2018 01:41:55 GMT

Would you be able to share the app to check out what might be going wrong?