20 Replies Latest reply: Feb 27, 2018 1:18 PM by Swati Tomar RSS

    Max data on the basis of field

    Swati Tomar

      Hi Team,

      Want to find  the max date on the basis of type Del and Serial no.

      Like to show only max Del Data .

      Input:

       

      QtyTypeDateSerial_noDealer
      1Del13/05/2017A1D01
      1Delrt15/05/2017A1D01
      1Del12/08/2017A2D02
      1Del13/08/2017A3D03
      1Delrt14/05/2017A3D03
      1Del15/08/2017A3D04
      1Del16/08/2017A4D05
      1Delrt16/08/2017A4D05
      1Del17/08/2017A4D05
      1Del18/08/2017A5D06
      1Delrt19/08/2017A5D06
      1Del19/08/2017A5D06

        output

       

      QtyTypeDateSerial_noDealer
      1Del12/08/2017A2D02
      1Del15/08/2017A3D03
      1Del17/08/2017A4D05
      1Del19/08/2017A5D06

       

       

      Can anyone provide the solution.

       

      Thanks in Advance..

        • Re: Max data on the basis of field
          Alessandro Saccone

          Try This:

           

          TMP:

          load * Inline [

          Qty, Type, Date, Serial_no, Dealer

          1, Del, 13/05/2017, A1, D01

          1, Delrt, 15/05/2017, A1, D01

          1, Del, 12/08/2017, A2, D02

          1, Del, 13/08/2017, A3, D03

          1, Delrt, 14/05/2017, A3, D03

          1, Del, 15/08/2017, A3, D04

          1, Del, 16/08/2017, A4, D05

          1, Delrt, 16/08/2017, A4, D05

          1, Del, 17/08/2017, A4, D05

          1, Del, 18/08/2017, A5, D06

          1, Delrt, 19/08/2017, A5, D06

          1, Del, 19/08/2017, A5, D06

          ];

           

           

          Left Join

          LOAD Type, Serial_no, Max(Date) as Date, '1' as This_is_the_Row Resident TMP

          Where Type = 'Del'

          Group by Type, Serial_no;

           

          You have to use the column "This_is_the_Row "

            • Re: Max data on the basis of field
              Swati Tomar

              tired but Didn't get any relevant answer by this .

                 

              DateQtyDealerSerial_noThis_is_the_RowType
              13/08/20171D03A3 Del
              13/05/20171D01A1 Del
              15/05/20171D01A1 Delrt
              14/08/20171D03A3 Delrt
              15/08/20171D04A3 Del
              16/08/20171D05A4 Del
              16/08/20171D05A4 Delrt
              17/08/20171D05A4 Del
              18/08/20171D06A5 Del
              19/08/20171D06A5 Del
              19/08/20171D06A5 Delrt
              12/08/20171D02A21Del
            • Re: Max data on the basis of field
              sahadev patil

              Hi Swati,

               

              Use this Expression

              =aggr(max(Date),Serial_no,Dealer)

              hope this will work.

              • Re: Max data on the basis of field
                Swati Tomar

                By mistaken written wrong date in front of type: Delrt from Serial A3

                1Delrt14/05/2017A3D03

                 

                Correct 1 is

                1Delrt14/08/2017A3D03
                • Re: Max data on the basis of field
                  Alessandro Saccone

                  I have tried with your data and the result is not what you show;

                   

                  Send me the exact set of data....

                  • Re: Max data on the basis of field
                    sahadev patil

                    I have used following expression

                     

                    =aggr(max({<Type={'Del'}>}Date),Serial_no)

                    its work fine.Max date.JPG

                      • Re: Max data on the basis of field
                        Swati Tomar
                        QtyTypeDateSerialDealer
                        1Del13/05/2016A1D01
                        1Delrt15/05/2016A1D01
                        1Del12/08/2017A1D01
                        1Del13/08/2017A3D03
                        1Delrt14/05/2017A3D03
                        1Del15/08/2017A3D04
                        1Delrt15/08/2017A3D04
                        1Del15/05/2018A3D04
                        1Del16/08/2017A4D05
                        1Delrt16/08/2017A4D05
                        1Del17/08/2017A4D05
                        1Del18/08/2017A5D06
                        1Delrt19/08/2017A5D06
                        1Del19/08/2017A5D06

                        I checked but issue is in case of Serial No. A1, A3 .

                        I want when user select 2016 or 2016 with month 05 , he will not any data. he will get the data only when he select 2017 or 2017 with month 08 month.

                        Same with A3.

                        So I decided to work in backend n remove the entries in backend only.

                         

                        Please check it

                          • Re: Max data on the basis of field
                            Sunny Talwar

                            May be this

                             

                            Table:

                            LOAD * INLINE [

                                Qty, Type, Date, Serial, Dealer

                                1, Del, 13/05/2016, A1, D01

                                1, Delrt, 15/05/2016, A1, D01

                                1, Del, 12/08/2017, A1, D01

                                1, Del, 13/08/2017, A3, D03

                                1, Delrt, 14/05/2017, A3, D03

                                1, Del, 15/08/2017, A3, D04

                                1, Delrt, 15/08/2017, A3, D04

                                1, Del, 15/05/2018, A3, D04

                                1, Del, 16/08/2017, A4, D05

                                1, Delrt, 16/08/2017, A4, D05

                                1, Del, 17/08/2017, A4, D05

                                1, Del, 18/08/2017, A5, D06

                                1, Delrt, 19/08/2017, A5, D06

                                1, Del, 19/08/2017, A5, D06

                            ];


                            Right Join (Table)

                            LOAD Dealer,

                            Serial,

                            Type,

                            Max(Date) as Date

                            Resident Table

                            Where Type = 'Del'

                            Group By Dealer, Serial, Type;

                             

                            Capture.PNG

                              • Re: Max data on the basis of field
                                Swati Tomar

                                Hi Sunny,

                                Thank you so much for your reply..

                                 

                                But I Want unique serial no. data and I think with the help of rowno() we can achieve it but not sure.

                                In short I want unique Serial Del type data but only latest del data.

                                Some Example:

                                For A3 serial no.  for last Del line should come i.e. 15./8/2017 only and

                                For A1 nothing should show as after Del , Delrt take place so don't want this line

                                For A4 16/10/2017 should come .

                                 

                                 

                                 

                                Qty, Type, Date, Serial, Dealer

                                1, Del, 13/08/2017, A3, D03

                                    1, Delrt, 14/05/2017, A3, D03

                                    1, Del, 15/08/2017, A3, D04

                                    1, Delrt, 15/08/2017, A3, D04

                                    1, Del, 15/08/2017, A3, D04

                                 

                                    1, Del, 13/05/2016, A1, D01
                                    1, Delrt, 15/05/2016, A1, D01

                                    1, Del, 13/08/2017, A4, D03

                                    1, Delrt, 14/05/2017, A4, D03

                                    1, Del, 16/10/2017, A4, D04

                                 

                                 

                                  • Re: Max data on the basis of field
                                    Sunny Talwar

                                    What is not right about the output you see in the attached file above?

                                      • Re: Max data on the basis of field
                                        Swati Tomar

                                        Hi Sunny,

                                        My purpose is to show Detail of Delivered item against multiple dimension like dealer, City , State , Serial no

                                        1. The output shared by you showing duplicate Serial No. ,and I want Max Del date data only

                                        Ex:

                                        Qty, Type, Date, Serial, Dealer

                                        1, Del, 13/08/2017, A3, D03

                                            1, Delrt, 14/05/2017, A3, D03

                                            1, Del, 15/08/2017, A3, D04

                                            1, Delrt, 15/08/2017, A3, D04

                                            1, Del, 15/05/2018, A3, D04

                                         

                                        Output: In year 2018 record of A3 Serial against dealer D04 will come not in year 2017 . Single Entry

                                        2. 1 more point as I mentioned in above reply . if I change the input like Del den Delrt , here in this case I want any data as Del date< Delrt Date.

                                        Ex: Input

                                        Qty, Type, Date, Serial, Dealer

                                        1, Del, 13/05/2016, A1, D01

                                        1, Delrt, 15/05/2016, A1, D01

                                        Output: No record for A1 serial will come

                                        3. It should work on the basis of Serial not Dealer . Should show the latest del data against Serial .

                                        Ex:

                                        Qty, Type, Date, Serial, Dealer

                                        1, Del, 13/08/2017, A4, D03

                                        1, Delrt, 14/05/2017, A4, D03

                                        1, Del, 16/10/2017, A4, D04 

                                        Output: (This line should come as last line is Del for Serial no. A4 with dealer D04 .If user choose year 2017 or 10 month of 2017)

                                          • Re: Max data on the basis of field
                                            Sunny Talwar

                                            One thing which has been confusing me is the fact that you mentioned that you only wanted to see Del, why is Delrt  showing up on your output above? I am somehow not able to follow the logic... did we misunderstood that you only needed Del?

                                              • Re: Max data on the basis of field
                                                Swati Tomar

                                                1. Where in output it is Delrt ??

                                                2. Yes , I want to show Latest Del Data only against the Serial no. .

                                                Note:

                                                >>>If after Delivery item get return and it has no further transaction den it will not come.(Del Date< Delrt Date will not show this entry)

                                                >>> Single Item can deliver multiple times but after every delivery , return should come den only that particular item can del again.

                                                So in above examples I'm showing the same.

                                                  • Re: Max data on the basis of field
                                                    Sunny Talwar

                                                    These rows are Delrt

                                                     

                                                    Capture.PNG

                                                     

                                                    Are these not the output? I might be going crazy, but I thought this is the output you are looking to get . If this isn't the output... would you be able to give me the exact rows that will be part of the output from these

                                                     

                                                    Capture.PNG

                                                     

                                                    Thanks,

                                                    Sunny

                                                      • Re: Max data on the basis of field
                                                        Swati Tomar

                                                        That was an Input not output.

                                                        I have marked the output with yellow color

                                                         

                                                         

                                                        For below table:

                                                        LOAD * INLINE [

                                                            Qty, Type, Date, Serial, Dealer

                                                            1, Del, 13/05/2016, A1, D01

                                                            1, Delrt, 15/05/2016, A1, D01

                                                            1, Del, 12/08/2017, A1, D01

                                                            1, Del, 13/08/2017, A3, D03

                                                            1, Delrt, 14/05/2017, A3, D03

                                                            1, Del, 15/08/2017, A3, D04

                                                            1, Delrt, 15/08/2017, A3, D04

                                                            1, Del, 15/05/2018, A3, D04

                                                            1, Del, 16/08/2017, A4, D05

                                                            1, Delrt, 16/08/2017, A4, D05

                                                            1, Del, 17/08/2017, A4, D05

                                                            1, Del, 18/08/2017, A5, D06

                                                            1, Delrt, 19/08/2017, A5, D06

                                                            1, Del, 19/08/2017, A5, D06

                                                            1, Del, 13/05/2017, A6, D01

                                                            1, Delrt, 15/06/2017, A6, D01

                                                            1, Del, 12/08/2017, A6, D01

                                                         

                                                          

                                                         

                                                        ];

                                                         

                                                        Output will Be

                                                        Qty, Type, Date, Serial, Dealer

                                                            1, Del, 12/08/2017, A1, D01

                                                             1, Del, 15/05/2018, A3, D04

                                                             1, Del, 17/08/2017, A4, D05

                                                            1, Del, 19/08/2017, A5, D06

                                                            1, Del, 12/08/2017, A6, D01

                                                         

                                                        Now you can see only last Del data against the Serial .I'm saying against the serial no. because in above ex. For Serial no. A3 dere are 2diff dealer but I'm showing data of only Dealer which the latest Del data..

                                                         

                                                        Sorry for so much of confusing..:(

                                                          • Re: Max data on the basis of field
                                                            Sunny Talwar

                                                            Try this

                                                             

                                                            Table:

                                                            LOAD * INLINE [

                                                                Qty, Type, Date, Serial, Dealer

                                                                1, Del, 13/05/2016, A1, D01

                                                                1, Delrt, 15/05/2016, A1, D01

                                                                1, Del, 12/08/2017, A1, D01

                                                                1, Del, 13/08/2017, A3, D03

                                                                1, Delrt, 14/05/2017, A3, D03

                                                                1, Del, 15/08/2017, A3, D04

                                                                1, Delrt, 15/08/2017, A3, D04

                                                                1, Del, 15/05/2018, A3, D04

                                                                1, Del, 16/08/2017, A4, D05

                                                                1, Delrt, 16/08/2017, A4, D05

                                                                1, Del, 17/08/2017, A4, D05

                                                                1, Del, 18/08/2017, A5, D06

                                                                1, Delrt, 19/08/2017, A5, D06

                                                                1, Del, 19/08/2017, A5, D06

                                                                1, Del, 13/05/2017, A6, D01

                                                                1, Delrt, 15/06/2017, A6, D01

                                                                1, Del, 12/08/2017, A6, D01

                                                            ];


                                                            Right Join (Table)

                                                            LOAD Serial,

                                                            Type,

                                                            Max(Date) as Date

                                                            Resident Table

                                                            Where Type = 'Del'

                                                            Group By Serial, Type;

                                                             

                                                            Capture.PNG

                                                              • Re: Max data on the basis of field
                                                                Swati Tomar

                                                                It is working fine but Sorry Sunny i forget to mention one scenario which one is imp.

                                                                Table:

                                                                LOAD * INLINE [

                                                                    Qty, Type, Date, Serial, Dealer

                                                                    1, Del, 13/05/2017, A6, D01

                                                                    1, Delrt, 15/06/2017, A6, D01

                                                                ];


                                                                Output: This record should not come as in this case " Delrt Date> Del Date"..


                                                                i have mentioned it in above replies but forget in last reply..

                                                                  • Re: Max data on the basis of field
                                                                    Sunny Talwar

                                                                    May be try this

                                                                     

                                                                    Table:

                                                                    LOAD * INLINE [

                                                                        Qty, Type, Date, Serial, Dealer

                                                                        1, Del, 13/05/2016, A1, D01

                                                                        1, Delrt, 15/05/2016, A1, D01

                                                                        1, Del, 12/08/2017, A1, D01

                                                                        1, Del, 13/08/2017, A3, D03

                                                                        1, Delrt, 14/05/2017, A3, D03

                                                                        1, Del, 15/08/2017, A3, D04

                                                                        1, Delrt, 15/08/2017, A3, D04

                                                                        1, Del, 15/05/2018, A3, D04

                                                                        1, Del, 16/08/2017, A4, D05

                                                                        1, Delrt, 16/08/2017, A4, D05

                                                                        1, Del, 17/08/2017, A4, D05

                                                                        1, Del, 18/08/2017, A5, D06

                                                                        1, Delrt, 19/08/2017, A5, D06

                                                                        1, Del, 19/08/2017, A5, D06

                                                                        1, Del, 13/05/2017, A6, D01

                                                                        1, Delrt, 15/06/2017, A6, D01

                                                                    ];


                                                                    Right Join (Table)

                                                                    LOAD Serial,

                                                                    'Del' as Type,

                                                                    Date

                                                                    Where Len(Trim(Date)) > 0;

                                                                    LOAD Serial,

                                                                    If(Max(If(Type = 'Delrt', Date)) <= Max(If(Type = 'Del', Date)), Max(If(Type = 'Del', Date))) as Date

                                                                    Resident Table

                                                                    Group By Serial;