19 Replies Latest reply: Aug 11, 2017 12:35 AM by Chanin D'Souza RSS

    Count Of fields Having Max ID

    Chanin D'Souza

      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.

        • Re: Count Of fields Having Max ID
          Chanty 4u

          try this

           

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

          • Re: Count Of fields Having Max ID
            Chanty 4u

            or this

             

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

            • Re: Count Of fields Having Max ID
              JOSE LUIS SENOVILLA YUNQUERA

              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

              • Re: Count Of fields Having Max ID
                Andrea Gigliotti

                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.

                • Re: Count Of fields Having Max ID
                  mayilvahanan ramasamy

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

                  • Re: Count Of fields Having Max ID
                    kaan erisen

                    You can use FirstSortedValue() if your data properly sorted.

                     

                    Table :

                    Column 1 (Measure) : Rowno()

                    Column 2 (Measure) : FirstSortedValue("Enquiry Id",-"Sr No")

                    Column 3 (Dimension) : "Customer Id"

                    Column 4 (Measure) : FirstSortedValue("Current Action",-"Sr No")


                    FirstSortedValue.png

                      • Re: Count Of fields Having Max ID
                        Chanin D'Souza

                        Thanks a Lot. It works like a charm.

                        Now Could You please help me with the Bar Graph Part of it .

                                  I want the Current Action As Dimension and Count(Customer Id) as Measure

                          • Re: Count Of fields Having Max ID
                            kaan erisen

                            Glad to hear that

                             

                            First of all, please mark the entry as CORRECT. This will help community members to find possible solutions easily


                            Bar Chart,

                            Dimension : "Current Action"

                            Measure : Count({<"Sr No"={$(=concat(aggr(max("Sr No"),"Customer Id"),','))}>} "Customer Id")

                             

                            FirstSortedValue.png

                              • Re: Count Of fields Having Max ID
                                Chanin D'Souza

                                Thanks a lot. Even the Bar Graph is working Perfectly fine .

                                Thanks once again. Cheers mate

                                  • Re: Count Of fields Having Max ID
                                    kaan erisen

                                    As an extra advice, it is better to handle this kind of needs in script side for better performance. You can flag the records that you need

                                     

                                    For example:

                                     

                                    temp_table:

                                    load * Inline [

                                    "Sr","Enq","Cust","Cur"

                                    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'

                                     

                                    table:

                                    load *,

                                    IF(Cust<>peek(Cust,RecNo(),'temp_table'),1,0) as LastOccFlag

                                    Resident temp_table;

                                     

                                    drop table temp_table;

                                     

                                     

                                    result will be:

                                     

                                    result.png

                                     

                                    Then you can use this flag on calculations.

                                    you can use count({<LastOccFlag={1}>} "Customer Id") instead of Count({<"Sr No"={$(=concat(aggr(max("Sr No"),"Customer Id"),','))}>} "Customer Id") for the bar chart that you already did.

                              • Re: Count Of fields Having Max ID
                                Chanin D'Souza

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

                              • Re: Count Of fields Having Max ID
                                scotly victor

                                TABLE:

                                 

                                 

                                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

                                ];

                                maptable:

                                mapping

                                load "Customer Id"&'-'&max as Key,RowNo() as row;

                                load "Customer Id",max(NUM#(PurgeChar("Enquiry Id",'ABC'))) as max resident TABLE GROUP BY "Customer Id";

                                load*,

                                ApplyMap('maptable',

                                "Customer Id"&'-'&NUM#(PurgeChar("Enquiry Id",'ABC')),null()) as "Sr n01" resident TABLE;

                                drop table TABLE;