topic Re: Data Issue in QlikView

Data Issue

Wed, 25 Nov 2020 16:16:04 GMT

I have data like below.

LeaseID SubLeaseID ExpDate

123 456 5/9/2016

123 678 5/9/2017

234 890 6/9/2016

Here when I have Multiple subleaseID's for one LeaseID I only want to pull the ID with the max expdate and I want to do it in the script level as this will be linked to another table with expenses.

I want the output to look like below.

LeaseID SubLeaseID ExpDate

123 678 5/9/2017

234 890 6/9/2016

How can I do this in the backend script ?

Thanks much.

Re: Data Issue

sunny_talwar — Fri, 06 May 2016 00:33:38 GMT

There are multiple ways you can do this. One of them would be this:

Method 1:

Table:

LOAD * Inline [

LeaseID, SubLeaseID, ExpDate

123, 456, 5/9/2016

123, 678, 5/9/2017

234, 890, 6/9/2016

];

Right Join(Table)

LOAD LeaseID,

Max(ExpDate) as ExpDate

Resident Table

Group By LeaseID;

Re: Data Issue

sunny_talwar — Fri, 06 May 2016 00:37:01 GMT

Or this

Method 2:

Table:

LOAD LeaseID,

FirstSortedValue(SubLeaseID, -ExpDate) as SubLeaseID,

Date(Max(ExpDate)) as ExpDate

Group By LeaseID;

LOAD * Inline [

LeaseID, SubLeaseID, ExpDate

123, 456, 5/9/2016

123, 678, 5/9/2017

234, 890, 6/9/2016

];

Method 3:

Table:

LOAD * Inline [

LeaseID, SubLeaseID, ExpDate

123, 456, 5/9/2016

123, 678, 5/9/2017

234, 890, 6/9/2016

];

FinalTable:

NoConcatenate

LOAD *

Resident Table

Where LeaseID <> Previous(LeaseID)

Order By LeaseID, ExpDate desc;

DROP Table Table;

Re: Data Issue

Fri, 06 May 2016 12:19:07 GMT

Thank you Sunny.I appreciate your help

I am sorry but now the request is pick the max amount.

My Data looks as below.

Lease:

LeaseID SubLeaseID ExpDate

123 456 5/9/2016

123 678 5/9/2017

234 890 6/9/2016

ExpTable:

SubleasID Exp

456 56,897

678 43,890

890 43,210

They want the o/p as below

LeaseID SubLeaseID ExpDate Exp

123 456 5/9/2016 56,897

234 890 6/9/2016 43,210

Can this be done ? How can i pick the max amount for each subspace ID. Thank you very much.

Re: Data Issue

sunny_talwar — Fri, 06 May 2016 12:26:04 GMT

Try this:

Table:

LOAD * Inline [

LeaseID, SubLeaseID, ExpDate

123, 456, 5/9/2016

123, 678, 5/9/2017

234, 890, 6/9/2016

];

MappingExpTable:

Mapping

LOAD * Inline [

SubleasID, Exp

456, 56897

678, 43890

890, 43210

];

FinalTable:

LOAD LeaseID,

FirstSortedValue(SubLeaseID, -Exp) as SubLeaseID,

FirstSortedValue(ExpDate, -Exp) as ExpDate,

Max(Exp) as Exp

Group By LeaseID;

LOAD *,

ApplyMap('MappingExpTable', SubLeaseID) as Exp

Resident Table;

DROP Table Table;

UPDATE:
You can also use Joins instead of ApplyMap if there are more fields in your ExpTable table. I did ApplyMap assuming there are only two fields, but in your case it might make sense to just to a left join.