topic Re: Get Latest Changed Value with Set Analysis in QlikView

Get Latest Changed Value with Set Analysis

santiago_respane — Wed, 25 Nov 2020 16:16:04 GMT

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,

Re: Get Latest Changed Value with Set Analysis

sunny_talwar — Fri, 17 Jun 2016 12:44:01 GMT

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

Re: Get Latest Changed Value with Set Analysis

swuehl — Fri, 17 Jun 2016 12:51:38 GMT

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
1	17/06/2016	100	72

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.

Re: Get Latest Changed Value with Set Analysis

sunny_talwar — Fri, 17 Jun 2016 12:53:29 GMT

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)

Re: Get Latest Changed Value with Set Analysis

swuehl — Fri, 17 Jun 2016 13:09:36 GMT

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

The sortable Aggr function is finally here!