Identify Pure/Mixed Expression

[Anonymous]

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

[sunny_talwar]

Try this

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

[Anonymous]

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. 

[sunny_talwar]

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