Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

chanin7893
New Contributor III

Count Of fields Having Max ID

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.

1 Solution

Accepted Solutions
chanin7893
New Contributor III

Re: Count Of fields Having Max ID

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")

19 Replies
sureshqv
Esteemed Contributor III

Re: Count Of fields Having Max ID

try this

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

sureshqv
Esteemed Contributor III

Re: Count Of fields Having Max ID

or this

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

chanin7893
New Contributor III

Re: Count Of fields Having Max ID

Doesn't work

chanin7893
New Contributor III

Re: Count Of fields Having Max ID

Even This Doesn't work

jsenivifor
New Contributor II

Re: Count Of fields Having Max ID

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
Honored Contributor II

Re: Count Of fields Having Max ID

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
New Contributor III

Re: Count Of fields Having Max ID

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

Re: Count Of fields Having Max ID

Hi

Try like this

Data:

LOAD *, RowNo() as RowID 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

];

Inner Join

LOAD [Customer Id], Max(RowID) as RowID Resident Data Group by [Customer Id];

chanin7893
New Contributor III

Re: Count Of fields Having Max ID

This is just sample data .. The actual data is much larger . I cannot use inline load then