topic Re: Values from latest date based on Uniqueid in QlikView

Values from latest date based on Uniqueid

Tue, 19 Jan 2016 13:17:20 GMT

Hi All,

I need to fetch the amount based on the Max of the date.

There are multiple values for each uniqueid.

Example

Date amount Uniqueid

11/27/2015 :.	23119079	TEFA_U4d11f4
11/26/2015 :.	22335359	TEFA_U4d11f4
11/30/2015 :.	8707045	TEFA_U4d11f4

In the above scenario i should Fetch the last amount 8707045.

Only the values from latest date based on Uniqueid. Attached the Qvw.

Please help me with this.

Regards,

Keerthi KS

Re: Values from latest date based on Uniqueid

Anonymous — Tue, 19 Jan 2016 13:20:05 GMT

like this?

=sum({<Date=, Date={'=$(=Date(max(Date),'MM/DD/YYYY'))'}>} Amount)

Re: Values from latest date based on Uniqueid

sunny_talwar — Tue, 19 Jan 2016 13:23:13 GMT

Try this:

Dimension: uniqueid

Expressions:

1) =Date(Max(ApprovedDate), 'MM/DD/YYYY')

2) =FirstSortedValue(Aggr(Sum(GrandTotal), uniqueid, ApprovedDate), -ApprovedDate)

Re: Values from latest date based on Uniqueid

Tue, 19 Jan 2016 13:24:19 GMT

I want to do it while loading. B

Re: Values from latest date based on Uniqueid

sunny_talwar — Tue, 19 Jan 2016 13:31:42 GMT

Try this:

Table:

LOAD

uniqueid,

grandtotal as GrandTotal,

Date(approvedo1,'M/D/YYYY h:mm:ss[.fff] TT') as ApprovedDate,

Date(Floor(approvedo1)) as Date

FROM

(qvd);

Right Join (Table)

LOAD uniqueid,

Max(Date) as Date

Resident Table

Group By uniqueid;

Re: Values from latest date based on Uniqueid

jagan — Wed, 20 Jan 2016 00:59:05 GMT

Hi,

Try like this in script

Data:

LOAD

uniqueid,

grandtotal as GrandTotal,

Date(approvedo1,'M/D/YYYY h:mm:ss[.fff] TT') as ApprovedDate

FROM

(qvd);

INNER JOIN(Data)

LOAD

uniqueid,

Date(Max(Date), 'M/D/YYYY h:mm:ss[.fff] TT') AS ApprovedDate

RESIDENT Data

Group By uniqueid;