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.
In script you want to do this or what.
aggregate table:
max(date) per project
(using Load... Group by...)
then left join your detailled table on the aggregate table
Try
load
max("Log Date") as "Log Date",
Project,
Status
From ...
Group by Project,
Status;
Hi Francisco,
the above solution work great, but as an additional information look at this:
i hope that helps
Beck
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
Let me Know about this
i prefer to load all entries and create a filter in the dashboard to show only last entry
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.
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
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 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!!! |
Dimension:
Project ,Log Date
Measure:
FirstsortedValue(Status,-Log Date)
Provided Log Date is formatted as Qlik Date.