topic Re: Get value with max transaction date in QlikView

Get value with max transaction date

dapostolopoylos — Wed, 11 Jun 2014 12:17:10 GMT

Hello everybody, i will need a little help on that...

Lets assume that i have the following data table:

LOAD * INLINE [

productId, description, Trans_date, Trans_id

4098, desc1, 2013-01-01, 1

4098, desc1, 2013-06-20, 2

4098, desc2, 2014-01-01, 3

4098, desc2, 2014-06-02, 4

4098, desc2, 2014-07-30, 5

4098, desc1, 2013-07-30, 6

];

I want to create a new field in my load script called for example "final_description" that will have the value of the "description" column where "Trans_date" = Max('Trans_date").

In my example the "final_description" should be equal to desc2 because Max('Trans_date") = 2014-07-30...

Any suggestions?

Re: Get value with max transaction date

Wed, 11 Jun 2014 12:21:46 GMT

Table:

LOAD * INLINE [

productId, description, Trans_date, Trans_id

4098, desc1, 2013-01-01, 1

4098, desc1, 2013-06-20, 2

4098, desc2, 2014-01-01, 3

4098, desc2, 2014-06-02, 4

4098, desc2, 2014-07-30, 5

4098, desc1, 2013-07-30, 6

];

TempTable:

Load productId,

description where Trans_date like max(Trans_date) as max_trans_date

Resident Table;

Inner Join (Table)

Load *

Resident TempTable;

Drop table TempTable;

Re: Get value with max transaction date

MayilVahanan — Wed, 11 Jun 2014 12:24:21 GMT

Try like this

Test:

LOAD * INLINE [

productId, description, Trans_date, Trans_id

4098, desc1, 2013-01-01, 1

4098, desc1, 2013-06-20, 2

4098, desc2, 2014-01-01, 3

4098, desc2, 2014-06-02, 4

4098, desc2, 2014-07-30, 5

4098, desc1, 2013-07-30, 6

];

Load FirstSortedValue(description, -Trans_date) as final_description, productId Resident Test Group by productId;

Re: Get value with max transaction date

Anonymous — Wed, 11 Jun 2014 12:51:06 GMT

Hi,

Try with this.It is working fine.

LOAD * INLINE [
    productId, description, Trans_date, Trans_id
    4098, desc1, 2013-01-01, 1
    4098, desc1, 2013-06-20, 2
    4098, desc2, 2014-01-01, 3
    4098, desc2, 2014-06-02, 4
    4098, desc2, 2014-07-30, 5
    4098, desc1, 2013-07-30, 6
];

Temp:
Load
max(Trans_date) as max_Trans_date
Resident Table;

Let vMaxTransDate=peek('max_Trans_date');

inner join(Table)
Load productId,
description as finalDesc
Resident Table
where Trans_date = $(vMaxTransDate) ;

drop table Temp;

Re: Get value with max transaction date

dapostolopoylos — Wed, 11 Jun 2014 12:59:31 GMT

Thanks everyone for their help!

The implementation i did is the following:

allTxn:

LOAD * INLINE [

productId, description, Trans_date, Trans_id

4098, desc1, 2013-01-01, 1

4098, desc1, 2013-06-20, 2

4098, desc2, 2014-01-01, 3

4098, desc2, 2014-06-02, 4

4098, desc2, 2014-07-30, 5

4098, desc1, 2013-07-30, 6

4091, desc3, 2012-05-06, 7

4091, desc4, 2014-05-27, 8

4091, desc3, 2012-05-16, 9

];

inner join

Load

productId,

firstsortedvalue(description, -Trans_date) as final_description

Resident allTxn

Group by productId;

Be well...