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 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!!!

my resulting table would be:

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

How should I do this?

Thankn you in advance,

FC.

[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

[its_anandrjs]

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

[francisco_cohen]

the code I've pasted above works fine, there is no issue with comma...

but I want to filter in my dashboard by the latest "Data" (date) for each unique "Project Code"

[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.

[francisco_cohen]

Hi Anand,

Did you see my last reply?

Can you help me please?

Thank you,

FC.