Values from latest date based on Uniqueid

[#1]

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

[Anonymous]

like this?

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

[sunny_talwar]

Try this:

Dimension: uniqueid

Expressions:

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

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

[#4]

I want to do it while loading. B

[sunny_talwar]

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;

[jagan · Accepted solution]

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;