Solved: Re: Count Of fields Having Max ID - Qlik Community

chanin7893 · ‎2017-08-08

Hello one and all,

I have The following table

Sr No Enquiry Id Customer Id Current Action

1 ABC1 AAA Not Interested

2 ABC2 BBB Order Placed

3 ABC3 BBB Material Supplied

4 ABC4 CCC Order Lost

5 ABC5 CCC AR Clear

6 ABC6 CCC Quotation Submitted

7 ABC7 DDD Interest Shown

8 ABC8 DDD Decision on Hold

9 ABC9 EEE Material Supplied

10 ABC10 FFF Negotiation Stage

11 ABC11 FFF Order Placed

12 ABC12 FFF Order Lost

13 ABC13 GGG Wrong Info

14 ABC14 GGG Not Interested

15 ABC15 HHH Product Info Requires

I want a Table As Follows:

Sr No Enquiry Id Customer Id Current Action

1 ABC1 AAA Not Interested

2 ABC3 BBB Material Supplied

3 ABC6 CCC Quotation Submitted

4 ABC8 DDD Decision on Hold

5 ABC9 EEE Material Supplied

6 ABC12 FFF Order Lost

7 ABC14 GGG Not Interested

8 ABC15 HHH Product Info Requires

I just want the last current action to be displayed taking the Max Value of the Enquiry Id

Also let me know if a Bar Chart for the same can be created as well . I want the Dimension as Current Action and the Measure as Count(Distinct Customer Id) Where Enquiry Id to be max (Enquiry Id)

Thank You in advance.

chanin7893 · ‎2017-08-11

Hi

Actually It Shows the correct count when on display, but however when you are selecting a particular dimension, then the count increases because it even takes the count that doesn't have a max [Sr No].

For Example the Graph shows [Order Lost] count as 1, but if you click on it the count increases to 2. This is because it even takes [Customer Id] CCC's [Order Lost] into consideration. So I did the necessary change in the equation and then the answer comes correct

The correct Expression is as follows

Count({<"Sr No"={ $(=Concat(aggr(max({1}"Sr No"),[Customer Id]),','))}>}"Customer Id")

View solution in original post

Chanty4u · ‎2017-08-08

try this

Count(If(Aggr(NODISTINCT Max(id), id) = dimension, id))

Chanty4u · ‎2017-08-08

or this

max(aggr(count(CID), curentaction, ID))

chanin7893 · ‎2017-08-08

Doesn't work

chanin7893 · ‎2017-08-08

Even This Doesn't work

jsenivifor · ‎2017-08-08

Hi,

You can include the following code when loading the script.

datos:

LOAD * inline [

Sr No, Enquiry Id, Customer Id, Current Action

1, ABC1 , AAA, Not Interested

2, ABC2 , BBB, Order Placed

3, ABC3 , BBB, Material Supplied

4, ABC4 , CCC, Order Lost

5, ABC5 , CCC, AR Clear

6, ABC6 , CCC , Quotation Submitted

7, ABC7 , DDD , Interest Shown

8, ABC8 , DDD , Decision on Hold

9, ABC9 , EEE , Material Supplied

10, ABC10, FFF , Negotiation Stage

11, ABC11, FFF , Order Placed

12, ABC12, FFF , Order Lost

13, ABC13, GGG , Wrong Info

14, ABC14, GGG , Not Interested

15, ABC15, HHH , Product Info Requires

];

qualify *;

datos_mod:

load

Rowno() as [Sr No],

LastValue([Enquiry Id]) as [Enquiry Id],

[Customer Id],

LastValue([Current Action]) as [Current Action]

resident datos

group by [Customer Id];

Good luck!

Jose Luis

agigliotti · ‎2017-08-08

try making a group by in your script like below:

load

Rowno() as [Sr No],

[Customer Id],

max([Current Action]) as [Last Action],

max([Enquiry Id]) as [Enquiry Id]

FROM ......your table

group by [Customer Id];

you don't need to use INLINE.

chanin7893 · ‎2017-08-08

This is just for 15 Values, I have a table that has around 50000 rows of data

MayilVahanan · ‎2017-08-08

Hi

Try like this

Data:

LOAD *, RowNo() as RowID inline [