7 Replies Latest reply: Mar 3, 2016 8:40 AM by Vitalii Chupryna RSS

    Load data with max date

    Vitalii Chupryna

      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
              Vitalii Chupryna

              I try to load data from file that contains historical data, but I need only last week with correct data.

              In example below I should load only week 2/6/2016

               

               

              Date  LYS $TYS $S%TYO $O%
              1.19.1324.226-7%13.678%
              7/26/201442.82665%2295%
              8.2.14-7.126-127%34-121%
              2/6/201638.633.715%22.671%
              2/13/2016 #DIV/0! #DIV/0
            • 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
                  Vitalii Chupryna

                  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;