Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
We value your feedback – take our 5-minute QlikView modernization survey
cancel
Showing results for 
Search instead for 
Did you mean: 
dapostolopoylos
Creator III
Creator III

Get value with max transaction date

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?

Father/Husband/BI Developer
1 Solution

Accepted Solutions
MayilVahanan

Hi

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;

Thanks & Regards,
Mayil Vahanan R

View solution in original post

4 Replies
Not applicable

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;

MayilVahanan

Hi

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;

Thanks & Regards,
Mayil Vahanan R
karthigayan
Creator II
Creator II

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;

dapostolopoylos
Creator III
Creator III
Author

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...

Father/Husband/BI Developer