get max data count of records

[paulwalker]

Hi Community,

I have 3 columns Name, Type and ID

Simple count I have to show which is working fine as below.

Created composite key like Name&'-'&Type and my expression aggregated based on Name.

below values are correct, but have to show Type value should be maximum count data.

Here first record is correct ABC-A, but second record should be BCA-D because the value of D is greater than C

Output should be

@tresesco, @MayilVahanan,  @Kushal_Chawda, @marcus_sommer, @stevedark, @sunny_talwar  and experts can you please help

Thanks in Advance !

[paulwalker]

Can anyone help on this ?

[Anil_Babu_Samineni]

Perhaps this? Sorry* I don't have Qlik view.

Dimension : =Name&'-'& Aggr(FirstSortedValue(Type, -Aggr(Max(Count), Type)), Name)

Expression: Sum(Count)

[Lisa_P]

This works except for Anil's Field being aggregated, should read:

Dimension : =Name&'-'& Aggr(FirstSortedValue(Type, -Aggr(Max(ID), Type)), Name)

[paulwalker]

Thanks for replay, below is my test data.

is there any possibility expression level ? (ID value I have alphanumeric so Max it won't work)

Test:
LOAD *, Name&'-'&Type as Name_Type;
LOAD * INLINE [
Name, Type, ID
ABC, B, 100B
ABC, A, 100A
ABC, A, 100A1
ABC, A, 100A2
ABC, A, 100A3
ABC, A, 100A4
ABC, A, 100A5
ABC, A, 100A6
ABC, A, 100A7
BCA, C, 100C1
BCA, C, 100C2
BCA, D, 100D1
BCA, D, 100D2
BCA, D, 100D3
BCA, D, 100D4
BCA, D, 100D5
];

[paulwalker]

Experts, Could you please help me on this !

[marcus_sommer]

It's not quite clear what do you want to do and what should be the expected result? What's the aim?

Why merging Name and Type to show the only the ones with the highest count - but then counting all ID's against it? That the real ID isn't numeric is not mandatory a big challenge because with functions like maxstring() you could also get these values and/or only certain parts of the string might be considered - but it needs a clear and consistent logic. 

[paulwalker]

Thanks for reply Marcus.

Users requirement, have to combine Name and Type.

Second chart is correct which is the count, but we have to append Type (Which is the highest value of Type) 

For ABC name Type A value is high 8, so record should be ABC-A and Count should be 9 

Final output is 

Hope you are clear.

[Anil_Babu_Samineni]

What is the outcome you expect from given data?

[marcus_sommer]

I think I wouldn't create a calculated dimension else displaying it only on an expression-level because the calculated dimension wouldn't really simplify the expressions and IMO the expressions would show the results in a more logical manner - maybe in this way: