# New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

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.

Tags (4)
1 Solution

Accepted Solutions
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
Esteemed Contributor III

## Re: Count Of fields Having Max ID

try this

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

Esteemed Contributor III

## Re: Count Of fields Having Max ID

or this

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

New Contributor III

## Re: Count Of fields Having Max ID

Doesn't work

New Contributor III

## Re: Count Of fields Having Max ID

Even This Doesn't work

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:

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

Honored Contributor II

## Re: Count Of fields Having Max ID

try making a group by in your script like below:

Rowno() as [Sr No],

[Customer Id],

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

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

group by [Customer Id];

you don't need to use INLINE.

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

MVP

## 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];

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