14 Replies Latest reply: Sep 11, 2017 5:05 PM by Francisco Cohen RSS

    Show Latest entry

    Francisco Cohen

      Hi all,

       

      I want to build a table that only shows the latest entry of each different occurence.

      My data is:

       

      Log DateProjectStatus
      12/08/2017Project 1going well
      15/08/2017Project 1crisis!
      14/08/2017Project 2i'm ok
      07/09/2017Project 3perfect
      06/09/2017Project 2great!!!

       

      my resulting table would be:

       

      Log DateProjectStatus
      15/08/2017Project 1crisis!
      06/09/2017Project 2great!!!
      07/09/2017Project 3perfect

       

       

      How should I do this?

      Thankn you in advance,

      FC.

        • Re: Show Latest entry
          Anand Chouhan

          In script you want to do this or what.

            • Re: Show Latest entry
              Francisco Cohen

              i prefer to load all entries and create a filter in the dashboard to show only last entry

                • Re: Show Latest entry
                  Shraddha Gajare

                  in that case, try below:

                   

                  Table1:

                  Load

                  Project &'-'& Status &'-'& "Log date" as Key,

                           Project,

                           Status,

                            "Log date"

                  From...;

                   

                  Left join (Table1)

                   

                  Load Project &'-'& Status &'-'& "Log date" as Key,

                  LatestFlag;

                   

                  Load Project,

                  Status

                  max("Log Date") as "Log Date",

                  'Latest' as LatestFlag

                  From ...

                   

                  Group by Project,

                                 Status;

                   

                  Use LatestFlag as Filter in Dashboard.

                    • Re: Show Latest entry
                      Francisco Cohen

                      Hi,

                      I'm not sure what I'm doing wrong... but I was not able to use your code and adapt to my case, the script is returning an error when loading...

                      This is my real script:

                       

                      FOR EACH Combined IN 'Andrew Clements-AC','Andre Melo-AM','Bruno Sequeira-BS','Duarte Teles-DT','Daniel Verbiest-DV','Fabio Andrade-FA','Gonçalo Neves-GN','Hugo Alves-HA','Miguel Araujo-MA','Marc Bartholomew-MB','Rodrigo Costa-RC','Rodrigo Ferreira-RF','Richard Genney-RG','Ricardo Laurêncio-RL','Tim de Groot-TG','Warwick Abrams-WA'

                      LET name = SubField('$(Combined)', '-', 1);

                      LET filename = SubField('$(Combined)', '-', 2);

                      [Table1]:

                      LOAD

                          "Project Code",

                          "Status",

                          Data,

                          Semaphore

                      FROM [lib://Fsbox PMO BI/Project Managers\PM Financial Info_$(filename).xlsx]

                      (ooxml, embedded labels, table is [Activity report]);

                      next Combined

                        • Re: Show Latest entry
                          Anand Chouhan

                          Try this with removing comma

                           

                          FOR EACH Combined IN 'Andrew Clements-AC','Andre Melo-AM','Bruno Sequeira-BS','Duarte Teles-DT','Daniel Verbiest-DV','Fabio Andrade-FA','Gonçalo Neves-GN','Hugo Alves-HA','Miguel Araujo-MA','Marc Bartholomew-MB','Rodrigo Costa-RC','Rodrigo Ferreira-RF','Richard Genney-RG','Ricardo Laurêncio-RL','Tim de Groot-TG','Warwick Abrams-WA'

                          LET name = SubField($(Combined), '-', 1);

                          LET filename = SubField($(Combined), '-', 2);

                          [Table1]:

                          LOAD

                              "Project Code",

                              "Status",

                              Data,

                              Semaphore

                          FROM [lib://Fsbox PMO BI/Project Managers\PM Financial Info_$(filename).xlsx]

                          (ooxml, embedded labels, table is [Activity report]);

                          next Combined

                      • Re: Show Latest entry
                        Anand Chouhan

                        Ok then try this but at least you have to introduce one flag field for latest date finding.

                         

                        Source:

                        Load date(Date#([Log Date],'DD/MM/YYYY')) as [Log Date],Project,Status,Project &'-'& [Log Date] as Key;

                        LOAD * Inline

                        [

                        Log Date, Project, Status

                        12/08/2017, Project 1, going well

                        15/08/2017, Project 1, crisis!

                        14/08/2017, Project 2, i'm ok

                        07/09/2017, Project 3, perfect

                        06/09/2017, Project 2, great!!!

                        ];

                         

                        MaxDate:

                        LOAD

                        Project,Date(Max([Log Date])) as LatestDate

                        Resident Source

                        Group By Project;

                         

                        Temp:

                        Left Join(Source)

                        LOAD

                        Project &'-'& LatestDate as Key, 1 AS LatestFlag

                        Resident MaxDate;

                        DROP Table MaxDate;

                         

                        OutPut

                        OP2.PNG

                          • Re: Show Latest entry
                            Francisco Cohen

                            Hi Anand,

                            Thank you for your time.

                            I'm using Qlik Sense Desktop version.

                             

                            My original script is the following:

                             

                            FOR EACH Combined IN 'Andrew Clements-AC','Andre Melo-AM','Bruno Sequeira-BS','Duarte Teles-DT','Daniel Verbiest-DV','Fabio Andrade-FA','Gonçalo Neves-GN','Hugo Alves-HA','Miguel Araujo-MA','Marc Bartholomew-MB','Rodrigo Costa-RC','Rodrigo Ferreira-RF','Richard Genney-RG','Ricardo Laurêncio-RL','Tim de Groot-TG','Warwick Abrams-WA'

                            LET name = SubField('$(Combined)', '-', 1);

                            LET filename = SubField('$(Combined)', '-', 2);

                            [Table1]:

                            LOAD

                                "Project Code",

                                "Status",

                                "LogDate",

                                "Semaphore"

                            FROM [lib://Fsbox PMO BI/Project Managers\PM Financial Info_$(filename).xlsx]

                            (ooxml, embedded labels, table is [Activity report]);

                            next Combined

                             

                            I would like to keep loading all entries from my source tables but in the dashboard would like to add a filter pane which would filter by latest "LogDate" for each unique "Project Code".

                             

                            Can you help?

                            Thks,

                            FC.

                            • Re: Show Latest entry
                              Francisco Cohen

                              Hi Anand,

                              Did you see my last reply?

                              Can you help me please?

                              Thank you,

                              FC.

                        • Re: Show Latest entry
                          m s

                          aggregate table:

                          max(date) per project

                           

                          (using Load... Group by...)

                           

                           

                          then left join your detailled table on the aggregate table

                          • Re: Show Latest entry
                            Shraddha Gajare

                            Try

                             

                            load

                             

                            max("Log Date") as "Log Date",

                            Project,

                            Status

                             

                            From ...

                            Group by Project,

                                           Status;

                            • Re: Show Latest entry
                              beck bakytbek

                              Hi Francisco,

                               

                              the above solution work great, but as an additional information look at this:

                               

                              http://help.qlik.com/en-US/qlikview/12.1/Subsystems/Client/Content/Scripting/StringAggregationFunctions/LastValue.htm

                               

                              i hope that helps

                               

                              Beck

                              • Re: Show Latest entry
                                Anand Chouhan

                                Better you can try and do this in the script part look at the sample example based on the data you are provided.

                                 

                                Source:

                                Load date(Date#([Log Date],'DD/MM/YYYY')) as [Log Date],Project, Status;

                                LOAD * Inline

                                [

                                Log Date, Project, Status

                                12/08/2017, Project 1, going well

                                15/08/2017, Project 1, crisis!

                                14/08/2017, Project 2, i'm ok

                                07/09/2017, Project 3, perfect

                                06/09/2017, Project 2, great!!!

                                ];


                                MaxDate:

                                LOAD

                                Project,Date(Max([Log Date])) as LatestDate

                                Resident Source

                                Group By Project;


                                NoConcatenate

                                Temp:

                                LOAD

                                LatestDate

                                Resident MaxDate;

                                DROP Table MaxDate;

                                 

                                NoConcatenate

                                LOAD * Resident Source Where Exists(LatestDate,[Log Date]);

                                DROP Table Source;

                                DROP Table Temp;


                                Output You get

                                OP.PNG


                                Let me Know about this

                                 

                                • Re: Show Latest entry
                                  AC BC

                                  If your are not looking for backend solution , use First sorted value in a straight table using the Date column to get the latest status.

                                   

                                  As per your data

                                   

                                  Log DateProjectStatus
                                  12/08/2017Project 1going well
                                  15/08/2017Project 1crisis!
                                  14/08/2017Project 2i'm ok
                                  07/09/2017Project 3perfect
                                  06/09/2017Project 2great!!!

                                   

                                  Dimension:

                                  Project ,Log Date

                                   

                                  Measure:

                                  FirstsortedValue(Status,-Log Date)

                                   

                                  Provided Log Date is formatted as Qlik Date.