7 Replies Latest reply: Mar 3, 2016 8:40 AM by Виталий Чуприна RSS

    Load data with max date

    Виталий Чуприна

      In files I get historical data and current week data. Date information can be in following formats:

       

      8.2.14

      2/6/2016

       

      I use alt function to determine type of date information:

      Date(Alt(Date#('$(vDate)', 'MM/DD/YYYY'), Date#('$(vDate)', 'MM.DD.YYYY')), 'YYYYMMDD')  as WEEK_ENDING_DATE

       

      How can I load only string with max date.

      I tried convert date into number and then use Max function, but for some reason it doesn't work and I get script error.

        • Re: Load data with max date
          balraj ahlawat

          What you are trying to achieve?

           

          Could you elaborate more?

           

          may be like this?

           

          Load

          *,

          Date(DateField,'DD/MM/YYYY') as DateField

          From Table

          where

          DateField=Date(max(DateField),'DD/MM/YYYY');

          • Re: Load data with max date
            Sunny Talwar

            May be this:

             

            Table:

            LOAD yourFields,

                      Date(Alt(Date#(DateField, 'M/D/YYYY'), Date#(DateField, 'M.D.YYYY')), 'YYYYMMDD')  as WEEK_ENDING_DATE

            FROM Table:

             

            Right Join (Table)

            LOAD Max(WEEK_ENDING_DATE) as WEEK_ENDING_DATE

            Resident Table;

              • Re: Load data with max date
                Виталий Чуприна

                Hi Sunny,

                 

                I tried your solution and it works butwith some questions.

                In my script I process data from different types of files and one of these types contains history data.

                When I added right join I got max week but for all files, I can't understand why this happened because I use if else statement .

                 

                For example:

                 

                IF  Wildmatch (vSheet, '1') then

                Data :

                Load * from ...

                ELSEIF  Wildmatch (vSheet, '2') then

                Data :

                Load * from ...

                ELSEIF  Wildmatch (vSheet, '3') then

                Data :

                Load * from ...

                 

                Right Join (Data)

                LOAD Max(WEEK_ENDING_DATE) as WEEK_ENDING_DATE

                Resident Data ;

                EndIF

                 

                I thought that I will load data with max week from files with sheet '3', but in result I got only files that have current max week(02/27) in file other file are ignored by script.

              • Re: Load data with max date
                Massimo Grossi

                // test data

                Source:

                load * inline [

                DateField

                8.2.14

                2/6/2016

                8.2.15

                6/30/2016

                7/1/2016

                ];

                 

                // add a date field

                Left Join (Source)

                LOAD    

                  DateField,     

                  Date( (Alt(Date#(DateField, 'M/D/YYYY'), Date#(DateField, 'M.D.YYYY'))), 'YYYYMMDD')  as DateNew

                Resident Source;

                 

                // keep the max

                Right Keep (Source)

                LOAD

                  Date( Max(DateNew), 'YYYYMMDD')  as DateNew

                Resident Source;