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.

[its_anandrjs]

In script you want to do this or what.

[mikaelsc]

aggregate table:

max(date) per project

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

then left join your detailled table on the aggregate table

[shraddha_g]

Try

load

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

Project,

Status

From ...

Group by Project,

               Status;

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

i hope that helps

Beck

[its_anandrjs]

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

[francisco_cohen]

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

[shraddha_g]

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.

[its_anandrjs]

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

[kkkumar82]

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.