27 Replies Latest reply: Nov 30, 2017 3:26 PM by Raghav B RSS

    pick max value status

    Raghav B

      Hi ,

       

      Below is my scenario

       

      Table1:

       

      Std, Prgrm,Course, Course status,date,....,.....

      1,A,A1,Cmplted,27Nov17,....,.....

      1,A,A1,Expired,26Nov17,....,.....

      2,B,B1,Completed,26Nov17,....,.....

      2,B,B1,Expired,29Nov17,....,.....

      2,B,B1,Expired,26Nov17,....,.....

      2,B,B1,Enrolled,30Nov17,....,.....

       

      Table2

      //(Resident Load from Table1 ,Removed Coursestatus to get max date for student,prgrm & course )

      //this is how i need data for Table1.

       

      Std, Prgrm,Course, date

      1,A,A1,27Nov17

      2,B,B1,29Nov17

      2,B,B1,30Nov17


      Now how can i get max max date status implied to main table?


      Thanks,

      Raghav

        • Re: pick max value status
          Cheenu Janakiram

          Hi Raghav,

           

          Why has the following not worked? Do you want to get Course Status in the table too?

           

          LOAD

          Std,

          Prgrm,

          Course,

          Max(Date) as Max_Date

          Resident Table 1

          GROUP BY

          Std,

          Prgrm,

          Course;

            • Re: pick max value status
              youssef belloum

              because his date is not recongnized as date so you can't detect the max.

               

              try to convert your "date" to a Date format before doing that

                • Re: pick max value status
                  Cheenu Janakiram

                  Good eye. I didn't realise the answer was that obvious.

                    • Re: pick max value status
                      youssef belloum

                      before go to the attached app,

                       

                      when you "Remove Course status to get max date for student,prgrm & course"


                      you will only get two lines as a result:


                       

                      Std Prgrm Course DATE
                      1AA127/11/17
                      2BB130/11/17

                       

                      the code to format the date, group by and get the max date:

                       

                      test:

                      LOAD Std, Prgrm,Course, [Course status],Date(MakeDate(right(date,2),num(month(date#(mid(date,3,3),'MMM'))),left(date,2)),'DD/MM/YY') as DATE

                      Inline [

                      Std, Prgrm,Course, Course status,date

                      1,A,A1,Cmplted,27Nov17

                      1,A,A1,Expired,26Nov17

                      2,B,B1,Completed,26Nov17

                      2,B,B1,Expired,29Nov17

                      2,B,B1,Expired,26Nov17

                      2,B,B1,Enrolled,30Nov17

                      ];

                       

                      test1:

                      load distinct Std, Prgrm,Course, max(DATE) as DATE

                      resident test

                      Group by Std, Prgrm,Course;

                       

                      DROP Table test;

                       

                       

                      see attached

                • Re: pick max value status
                  vishal waghole

                  Try it,

                   

                  Table1:

                  LOAD *,

                  Date(Date#(Mid(date,1,2)&'/'&Mid(date,3,3)&'/'&Mid(date,6,2),'DD/MMM/YY'),'MM/DD/YYYY') as NewDate

                  Inline [

                  Std, Prgrm,Course, Course status,date

                  1,A,A1,Cmplted,27Nov17

                  1,A,A1,Expired,26Nov17

                  2,B,B1,Completed,26Nov17

                  2,B,B1,Expired,29Nov17

                  2,B,B1,Expired,26Nov17

                  2,B,B1,Enrolled,30Nov17

                  ];

                   

                  NoConcatenate

                  Table2:

                  LOAD Std,

                  Prgrm,

                  Course,

                  Date(Max(NewDate)) as MaxDate

                  Resident Table1

                  Group by Std, Prgrm, Course;

                   

                  drop Table Table1;

                   

                  Regards,

                  Vishal Waghole

                    • Re: pick max value status
                      Raghav B

                      Thank you all for your replies. plz ignore Date format.it was correctly showing up in my script.

                       

                      What my requirement here is i need only max date status from table 1 to be picked up. for my code in table 2.

                       

                      complete row data for max date. at each column stdname,program,course,status,....

                        • Re: pick max value status
                          youssef belloum

                          here it is another time with a group by on allt the columns:

                           

                          test:

                          LOAD Std, Prgrm,Course, [Course status],Date(MakeDate(right(date,2),num(month(date#(mid(date,3,3),'MMM'))),left(date,2)),'DD/MM/YY') as DATE

                          Inline [

                          Std, Prgrm,Course, Course status,date

                          1,A,A1,Cmplted,27Nov17

                          1,A,A1,Expired,26Nov17

                          2,B,B1,Completed,26Nov17

                          2,B,B1,Expired,29Nov17

                          2,B,B1,Expired,26Nov17

                          2,B,B1,Enrolled,30Nov17

                          ];

                           

                          NoConcatenate

                          test1:

                          load distinct Std, Prgrm,Course,[Course status], max(DATE) as DATE

                          resident test

                          Group by Std, Prgrm,Course,[Course status];

                           

                          DROP Table test;


                          result:

                          Std Prgrm Course Course status DATE
                          1AA1Cmplted27/11/17
                          1AA1Expired26/11/17
                          2BB1Expired29/11/17
                          2BB1Completed26/11/17
                          2BB1Enrolled30/11/17


                          see attached


                      • Re: pick max value status
                        vishal waghole

                        attachment for your referance

                          • Re: pick max value status
                            Raghav B

                            Hi Vishal, with your script i got below data. how ever i need status also for it. plz help.

                            Untitled.jpg

                              • Re: pick max value status
                                Sunny Talwar

                                Why don't you just do this

                                 

                                Table:

                                LOAD Std,

                                    Prgrm,

                                    Course,

                                    Course status,

                                    date

                                FROM ....;

                                 

                                Right Join (Table)

                                LOAD Std,

                                    Prgrm,

                                    Course

                                    Max(date) as date

                                Resident Table

                                Group By Std, Prgrm, Course;

                                 

                                Basically Right Join will make sure to keep only those rows in Table where the Date is Max(date) grouped by Std, Prgrm, Course

                                  • Re: pick max value status
                                    Raghav B

                                    WITH right join i got below data.

                                    right join.jpg

                                    Table1:

                                    LOAD *,

                                    Date(Date#(Mid(date,1,2)&'/'&Mid(date,3,3)&'/'&Mid(date,6,2),'DD/MMM/YY'),'MM/DD/YYYY') as NewDate

                                    Inline [

                                    Std, Prgrm,Course, Course status,date

                                    1,A,A1,Cmplted,27Nov17

                                    1,A,A1,Expired,26Nov17

                                    2,B,B1,Completed,26Nov17

                                    2,B,B1,Expired,29Nov17

                                    2,B,B1,Expired,26Nov17

                                    2,B,B1,Enrolled,30Nov17

                                    ];

                                     

                                    Right Join

                                    Table2:

                                    LOAD Std,

                                    Prgrm,

                                    Course,

                                    Date(Max(NewDate)) as MaxDate

                                    Resident Table1

                                    Group by Std, Prgrm, Course;

                                      • Re: pick max value status
                                        Sunny Talwar

                                        Don't rename Max(Date) to MaxDate... keep the same name, so that you right join on Date also

                                          • Re: pick max value status
                                            Sunny Talwar

                                            Like this

                                             

                                            Table1:

                                            LOAD *,

                                            Date(Date#(Mid(date,1,2)&'/'&Mid(date,3,3)&'/'&Mid(date,6,2),'DD/MMM/YY'),'MM/DD/YYYY') as NewDate

                                            Inline [

                                            Std, Prgrm,Course, Course status,date

                                            1,A,A1,Cmplted,27Nov17

                                            1,A,A1,Expired,26Nov17

                                            2,B,B1,Completed,26Nov17

                                            2,B,B1,Expired,29Nov17

                                            2,B,B1,Expired,26Nov17

                                            2,B,B1,Enrolled,30Nov17

                                            ];

                                             

                                            Right Join

                                            Table2:

                                            LOAD Std,

                                            Prgrm,

                                            Course,

                                            Date(Max(NewDate)) as NewDate

                                            Resident Table1

                                            Group by Std, Prgrm, Course;

                                              • Re: pick max value status
                                                Raghav B

                                                Thank you Sunny

                                                • Re: pick max value status
                                                  Raghav B

                                                  hi sunny,

                                                   

                                                  In my main app(table1) there are few rows with blank dates. i need them as well along with these max dates. If i do right join they are missing....

                                                  bcz based up them program status(one more new column) is to be derived

                                                  how to achive this. for blank date i gave like this If(isnull(date,1,0).as indicator.

                                                    • Re: pick max value status
                                                      Sunny Talwar

                                                      May be like this

                                                       

                                                      Table1:

                                                      LOAD *,

                                                      Date(Alt(Date#(Mid(date,1,2)&'/'&Mid(date,3,3)&'/'&Mid(date,6,2),'DD/MMM/YY'), Today()),'MM/DD/YYYY') as NewDate

                                                      Inline [

                                                      Std, Prgrm,Course, Course status,date

                                                      1,A,A1,Cmplted,27Nov17

                                                      1,A,A1,Expired,26Nov17

                                                      2,B,B1,Completed,26Nov17

                                                      2,B,B1,Expired,29Nov17

                                                      2,B,B1,Expired,26Nov17

                                                      2,B,B1,Enrolled,30Nov17

                                                      ];

                                                       

                                                      Right Join

                                                      Table2:

                                                      LOAD Std,

                                                      Prgrm,

                                                      Course,

                                                      Date(Max(NewDate)) as NewDate

                                                      Resident Table1

                                                      Group by Std, Prgrm, Course;

                                                        • Re: pick max value status
                                                          Raghav B

                                                          it is working. but what would this script do.

                                                            • Re: pick max value status
                                                              Sunny Talwar

                                                              Alt function picks the first non-null value from the list of value provided. When Date is null, it will pick Today() as date. This helps in when you do the join.

                                                               

                                                              Does that make sense?

                                                                • Re: pick max value status
                                                                  Raghav B

                                                                  ok. but what if i need the maxdate field to be blank instead of today() .

                                                                  can i give this way?

                                                                  Date(Alt(Date#(Mid(date,1,2)&'/'&Mid(date,3,3)&'/'&Mid(date,6,2),'DD/MMM/YY'),'-'),'MM/DD/YYYY') as NewDate

                                                                  this is bcz in my case enroll status should come only when course date is null

                                                                    • Re: pick max value status
                                                                      Sunny Talwar

                                                                      If you don't want to get rid of Null Dates, you can create a temporary date field for the purposes of join like this

                                                                       

                                                                      Table1:

                                                                      LOAD *,

                                                                      Date(Alt(Date#(Mid(date,1,2)&'/'&Mid(date,3,3)&'/'&Mid(date,6,2),'DD/MMM/YY'), Today()),'MM/DD/YYYY') as NewDateTemp,

                                                                      Date(Date#(Mid(date,1,2)&'/'&Mid(date,3,3)&'/'&Mid(date,6,2),'DD/MMM/YY'),'MM/DD/YYYY') as NewDate

                                                                      Inline [

                                                                      Std, Prgrm,Course, Course status,date

                                                                      1,A,A1,Cmplted,27Nov17

                                                                      1,A,A1,Expired,26Nov17

                                                                      2,B,B1,Completed,26Nov17

                                                                      2,B,B1,Expired,29Nov17

                                                                      2,B,B1,Expired,26Nov17

                                                                      2,B,B1,Enrolled,30Nov17

                                                                      ];

                                                                       

                                                                      Right Join

                                                                      Table2:

                                                                      LOAD Std,

                                                                      Prgrm,

                                                                      Course,

                                                                      Date(Max(NewDateTemp)) as NewDateTemp

                                                                      Resident Table1

                                                                      Group by Std, Prgrm, Course;

                                                                       

                                                                      The problem is that you cannot find a Max of '-' and that is why it won't work...

                                                                       

                                                                      Alternatively, you can create a flag for null dates

                                                                       

                                                                      If(Len(Trim(date)) = 0, 1, 0) as DateNullFlag

                                                                      and then you can replace your NewDate nulls to be today's date

                                                    • Re: pick max value status
                                                      Raghav B

                                                      In my final table I need only below data as they have maxdate with final status. Plz let me know how to achieve this.

                                                      result:

                                                      Std

                                                      Prgrm

                                                      Course

                                                      Course status

                                                      DATE

                                                      1

                                                      A

                                                      A1

                                                      Cmplted

                                                      27/11/17

                                                      2

                                                      B

                                                      B1

                                                      Enrolled

                                                      30/11/17

                                                        • Re: pick max value status
                                                          youssef belloum

                                                          You can't, because when you add "Course status" on the table, you can't ignore these lines:

                                                           

                                                          2BB1Completed26/11/17
                                                          2BB1Expired29/11/17

                                                           

                                                          so for the combination

                                                          2

                                                          B

                                                          B1

                                                           

                                                          you will have three lines:

                                                           

                                                          2BB1Expired29/11/17
                                                          2BB1Completed26/11/17
                                                          2BB1Enrolled30/11/17
                                                    • Re: pick max value status
                                                      Cheenu Janakiram

                                                      Hi Raghav,

                                                       

                                                      If you want to add Course status, I would try the following. However, it will only work if all Corse Statuses have different and incrementing dates. Otherwise, if there is a Enrolled and Completed sequence that are on same date, this would not work. If this is the case, try "numbering" the statuses with a Dual function and "maxing" on that. please feel free to give more detdta information.

                                                       

                                                      Table2:

                                                      LOAD

                                                      *,

                                                      Std & '_' & Prgrm & '_' & Course & Max_Data as FK01;

                                                      LOAD

                                                      Std,

                                                      Prgrm,

                                                      Course,

                                                      Max(Date) as Max_Date

                                                      Resident Table 1

                                                      GROUP BY

                                                      Std,

                                                      Prgrm,

                                                      Course;

                                                       

                                                      LEFT JOIN(Table2)

                                                      LOAD

                                                      Std & '_' & Prgrm & '_' & Course & Max_Data as FK01,

                                                      [Course Status]

                                                      RESIDENT Table1;

                                                      • Re: pick max value status
                                                        Cheenu Janakiram

                                                        One mistake in code. Try this:

                                                         

                                                        Table2:

                                                        LOAD

                                                        *,

                                                        Std & '_' & Prgrm & '_' & Course & Max_Date as FK01;

                                                        LOAD

                                                        Std,

                                                        Prgrm,

                                                        Course,

                                                        Max(Date) as Max_Date

                                                        Resident Table 1

                                                        GROUP BY

                                                        Std,

                                                        Prgrm,

                                                        Course;

                                                         

                                                        LEFT JOIN(Table2)

                                                        LOAD

                                                        Std & '_' & Prgrm & '_' & Course & Date as FK01,

                                                        [Course Status]

                                                        RESIDENT Table1;

                                                         

                                                        Ideally, you have date/time, rather than date. As explained before, if 2 statuses occur on same date, this won't work. In this case, I would do an inline mapping load for giving numerical values to the status, then try the same technique as above but then the 2nd table using a "max" on the field that contains 'mapped status number'.

                                                         

                                                        Can you share exact script for editing and replying to you?

                                                        • Re: pick max value status
                                                          Cheenu Janakiram

                                                          The point is to first find the max date and then a logic to find the "max status" that you are looking for. Then do a join on a concatenated field of all the other fields to find the right corresponding "status" to its record.

                                                           

                                                          Hope that makes sense.

                                                          • Re: pick max value status
                                                            Raghav B

                                                            Thank you All