Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
santiago_respane
Specialist
Specialist

Get Latest Changed Value with Set Analysis

Hi Experts,

i am facing the following issue, i have to display the current value of product and the last changed value.

Important: I need the latest value that changed, not the value from the Current Date - 1

Prod      Date               Value

1           17/06/2016      100

1           15/06/2016      100

1           14/06/2016      100

1           13/06/2016      100

1           12/06/2016      72

1           11/06/2016      72

And the desired output must be:

Prod     Date               Current Value     Last Value

1          17/06/2016      100                    72

I can see the solution via scripting but, is this possible with set analysis?

Any help will be appreciated.

Thanks in advance.

Kind regards,

4 Replies
sunny_talwar

May be try this:

Dimension: Prod

Expression:

1) Date(Max(Date))

2) FirstSortedValue(Value,  -Date)

3) FirstSortedValue(Value, Date)

You want the last changed value and not the last value

swuehl
MVP
MVP

Maybe something like

Prod Date(Max(Date)) FirstSortedValue(Value,-Date) =FirstSortedValue( Aggr( If( Value <> TOP(Value),Value), Prod,Date), -Aggr(If( Value <> TOP(Value),Date),Prod,Date) )
17/06/2016 100 72
117/06/201610072

edit:

Your Date values would need to have a chronological load order (either ascending or descending, you can adjust the TOP() / BOTTOM() and minus signs).

Or if you are using QV12 or sense, use structured parameter with the aggr() function.

sunny_talwar

May be a hybrid approach:

Table:

LOAD * Inline [

Prod,      Date,              Value

1,          17/06/2016,      100

1,          15/06/2016,      100

1,          14/06/2016,      100

1,          13/06/2016,      100

1,          12/06/2016,      72

1,          11/06/2016,      72

1, 10/06/2016,  82

];

FINAL_TABLE:

LOAD *,

  If(Prod = Previous(Prod) and Value = Previous(Value), Peek('Flag'), RangeSum(Peek('Flag'), 1)) as Flag

Resident Table

Order By Prod, Date;

DROP Table Table;

Dimension:

Prod

Expressions:

FirstSortedValue(DISTINCT Value,  -Flag)

FirstSortedValue(DISTINCT Value,  -Flag, 2)

Capture.PNG

swuehl
MVP
MVP

If you need to learn about structured parameter and the issue with aggr() dimension sorting:

The sortable Aggr function is finally here!