Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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
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"
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.
Hi Anand,
Did you see my last reply?
Can you help me please?
Thank you,
FC.