14 Replies Latest reply: Apr 4, 2014 7:08 AM by Ravi Kumar RSS

    Logic

    AVIRAL NAG

      Hi Community

       

      I have one issue:


      Load * Inline [

      Key,   Date1  ,        Date2
      AAA,   21/3/2014 ,     4/4/2014
      AAA,   22/3/2014 ,    10/4/2014
      AAA,   24/3/2014 ,     8/4/2014

      BBB,   20/3/2014 ,    15/4/2014

      BBB,   25/3/2014 ,     17/4/2014

      ];


      Desired Output Required:

       

      Key     Date1              Date2

      AAA    24/3/2014      10/4/2014

      BBB   25/3/2014       15/4/2014


       

      Need Suggestions

       

       

      Regards

      Aviral Nag

        • Re: Logic
          sivaraj seeman

          Check your date format it might be a string value. Convert to date format and apply your logic.

          One way to convert string is Date(Date#(Date1,'dd/m/yyyy'),'DD/MM/YYYY')

            • Re: Logic
              AVIRAL NAG

              This is not Date Format Issue. This is related to the Logic.

                • Re: Re: Logic
                  Ravi Kumar

                  Use this script

                   

                   

                  A:

                  Load * Inline [

                  Key, Date1, Date2
                  AAA, 21/3/2014, 4/4/2014
                  AAA, 22/3/2014, 10/4/2014
                  AAA, 24/3/2014, 8/4/2014
                  BBB, 20/3/2014, 15/4/2014
                  BBB, 25/3/2014, 17/4/2014

                  ]
                  ;

                  Left Join
                  B:
                  Load
                  Key,
                  Date(Max(Date#(Date1,'DD/MM/YYYY')),'DD/MM/YYYY') as MaxDate1,
                  Date(Max(Date#(Date2,'DD/MM/YYYY'),2),'DD/MM/YYYY') as MaxDate2
                  Resident A
                  Group By Key;

                    • Re: Logic
                      AVIRAL NAG

                      Thanks for your reply.

                       

                      I need Max(date,2) As per the Date1. Youe Logic doesn't give me desired result. Kindly see the output required in the top post.

                        • Re: Re: Logic
                          Ravi Kumar

                          see the modified one

                           

                           

                          A:

                          Load * Inline [

                          Key, Date1, Date2
                          AAA, 21/3/2014, 4/4/2014
                          AAA, 22/3/2014, 10/4/2014
                          AAA, 24/3/2014, 8/4/2014
                          BBB, 20/3/2014, 15/4/2014
                          BBB, 25/3/2014, 17/4/2014

                          ]
                          ;

                          B:
                          Load
                          Key,
                          Date(Max(Date#(Date1,'DD/MM/YYYY')),'DD/MM/YYYY') as MaxDate1
                          Resident A
                          Group By Key;

                          C:
                          Load *
                          where MaxDate2 <> Null();

                          NoConcatenate
                          Load
                          Key,
                          If(Key='AAA',Date(Max(Date#(Date2,'DD/MM/YYYY')),'DD/MM/YYYY')) as MaxDate2
                          Resident A
                          Group By Key;

                          D:
                          Load *
                          where MaxDate2 <> Null();

                          NoConcatenate
                          Load
                          Key,
                          If(Key='BBB',Date(Min(Date#(Date2,'DD/MM/YYYY')),'DD/MM/YYYY')) as MaxDate2
                          Resident A
                          Group By Key;

                  • Re: Logic
                    Andrei Kaliahin

                    Hi,

                     

                    I can suggest you to separate your table on 2 tables, because of extreme values are in different rows.

                    I would made first table with Key and Max(Date1), second table with Key and Max(Date2) and afterwards join tables or use ApplyMap()

                     

                    Hope it helps.

                     

                    Andrei

                    • Re: Logic
                      Nizam HM

                      Try  this one it will work

                       

                       

                      TableA:

                      Load * Inline [

                      Key,   Date1,        Date2

                      AAA,   21/3/2014,     4/4/2014

                      AAA,   22/3/2014,    10/4/2014

                      AAA,   24/3/2014,     8/4/2014

                      BBB,   20/3/2014,    15/4/2014

                      BBB,   25/3/2014,     17/4/2014

                      ];

                      //NoConcatenate

                      //LOAD max(Date1) as DAte3,Key Resident TableA Group by Key;

                       

                       

                      load MinString(Date1) as Date1,Key

                      Resident TableA

                      group by Key,1;

                       

                       

                      drop table TableA;

                      • Re: Logic
                        Tresesco B

                        Use a straight table;

                        dimension: Key

                        expression1: Max(Date1)

                        expression2: Max(Date2)

                        • Re: Logic
                          Amay Patil

                          hey check this..

                           

                          3.png

                          4.png

                          • Re: Logic
                            Tresesco B

                            Now I got your logic. Try this expression for Date2:

                             

                            =FirstSortedValue(Date2,-Date1, 2)

                             

                            Dimension: Key