8 Replies Latest reply: Jan 14, 2012 7:31 AM by gerardo.pauza RSS

    last date modified syntax

      Hi everyone,

           i have a table with this kind of information:

       

      1     msantiago     12/05/2001     NewYork     online

      2     msantiago     12/06/2001     NewYork     offline

      3     msantiago     12/07/2001     Boston        online

       

      and i need to find the last status of a person in every city, so i need a table like this:

       

      2     msantiago     12/06/2001     NewYork     offline

      3     msantiago     12/07/2001     Boston        online

       

      without the line:

      1     msantiago     12/05/2001     NewYork     online

       

      the problem is i don't know the syntax i have to use.....

       

      thanks

      gerardo

        • Re: last date modified syntax
          Miguel Angel Baeyens de Arce

          Hi Gerardo,

           

          Check the following script. DataFromSource is the original data, coming from the source (database, excel file, etc). There are no transformations here. DataTemp checks for the highest value in date in the same city, and stores this value in a new field named "DateMax". Finally, DataFinal loads all records where its original Date field equals to the highest date for that city.

           

          DataFromSource:
          LOAD * INLINE [
          Id, Name, Date, City, Status
          1, msantiago, 12/05/2001, NewYork, online
          2, msantiago, 12/06/2001, NewYork, offline
          3, msantiago, 12/07/2001, Boston, online
          ];
          
          DataTemp:
          LOAD *,
               If(City = Previous(City), Date(RangeMax(Date, Peek('DateMax'))), Date) AS DateMax
          RESIDENT DataFromSource
          ORDER BY City, Name ASC, Date DESC;
          
          DataFinal:
          NOCONCATENATE LOAD *
          RESIDENT DataTemp
          WHERE Date = DateMax;
          
          DROP TABLES DataFromSource, DataTemp;
          

           

          Hope that helps.

           

          Miguel

            • Re: last date modified syntax

              hI Miguel, i think im doing something wrong because there are script errors:

              i change city by city_id and name by email.

               

              Field not found

              SUBSCRIPTIONS_TEMP:

              LOAD *,

                         If(city_id = Previous(city_id), date(RangeMax(modified, Peek('DateMax'))), modified) AS DateMax

              RESIDENT SUBSCRIPTIONS

              ORDER BY city_id, email ASC, modified DESC

               

              and

               

              Table not found

              SUBSCRIPTIONS_FINAL:

              NOCONCATENATE LOAD *

              RESIDENT SUBSCRIPTIONS_TEMP

              WHERE modified = DateMax

               

              any idea?

               

              the whole script is this:

              LOAD

                  Year(created) & Num(Month(created),'00') & Num(Day(created),'00') as %Key_GENERACION_SUBSCRIPTIONS,

                  city_id,

                  created,

                  email,

                  id as subscriptions_id,

                  is_voluntary,

                  is_subscribed,

                  modified,

                  user_id

              FROM [..\QVD\SUBSCRIPTIONS.QVD] (qvd);

               

              SUBSCRIPTIONS_TEMP:

              LOAD *,

                         If(city_id = Previous(city_id), date(RangeMax(modified, Peek('DateMax'))), modified) AS DateMax

              RESIDENT SUBSCRIPTIONS

              ORDER BY city_id, email ASC, modified DESC;

               

              SUBSCRIPTIONS_FINAL:

              NOCONCATENATE LOAD *

              RESIDENT SUBSCRIPTIONS_TEMP

              WHERE modified = DateMax;

               

              DROP TABLES SUBSCRIPTIONS, SUBSCRIPTIONS_TEMP;

               

              thank you!

                • Re: last date modified syntax
                  Miguel Angel Baeyens de Arce

                  Hi Gerardo,

                   

                  The script you posted starts with a LOAD. Add a label "SUBSCRIPTIONS:" (uppercase) before the LOAD line to make sure the RESIDENT will take this name. If QlikView says table not found is likely because the first LOAD in your code above is labelled anyway but "SUBSCRIPTIONS".

                   

                  Hope that helps.

                   

                  Miguel

                    • Re: last date modified syntax

                      sorry miguel....de label "subscriptions" is there...

                      again:

                       

                      SUBSCRIPTIONS:

                      LOAD

                          Year(created) & Num(Month(created),'00') & Num(Day(created),'00') as %Key_GENERACION_SUBSCRIPTIONS,

                          city_id,

                          created,

                          email,

                          id as subscriptions_id,

                          is_voluntary,

                          is_subscribed,

                          modified,

                          user_id

                      FROM [..\QVD\SUBSCRIPTIONS.QVD] (qvd);

                       

                      SUBSCRIPTIONS_TEMP:

                      LOAD *,

                                 If(city_id = Previous(city_id), date(RangeMax(modified, Peek('DateMax'))), modified) AS DateMax

                      RESIDENT SUBSCRIPTIONS

                      ORDER BY city_id, email ASC, modified DESC;

                       

                      SUBSCRIPTIONS_FINAL:

                      NOCONCATENATE LOAD *

                      RESIDENT SUBSCRIPTIONS_TEMP

                      WHERE modified = DateMax;

                       

                      DROP TABLES SUBSCRIPTIONS, SUBSCRIPTIONS_TEMP;

                • last date modified syntax
                  Rob Wunderlich

                  If you don't need a data table, but just a chart, you can do it in a straight table chart.

                   

                  Dimension:

                  Name

                  City

                   

                  Expression:

                  =FirstSortedValue(Status, -Date)

                   

                  If you want to include the Date in the chart as well add:

                  =date(max(Date))

                   

                  -Rob

                  http://robwunderlich.com