Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

max value, how to load it

The main table is called bde_1.  I want to left join a table called t_reg to it.  Here is what I have

I do a left join as

left join (bde_1)

LOAD

    [ ID NUMBER],

    [FEE AMOUNT],

    [TPR_DATE]

from $(qvd_layer_2_path)T_REG.qvd(qvd)

where [FEE AMOUNT] >0;

My question is how can I load this in descending order by TPR Date and pick up the last transaction by tpr_date.  So if I have the following:

ID NUMBER        FEE AMOUNT     TPR DATE

11111                       2001                 09/26/2015

11111                       65221               09/22/2015

I would want the max date and pick up the transaction with the most recent date or in this case 9/26/2015

4 Replies
MarcoWedel

What is your expected result in bde_1?

regards

Marco

Gysbert_Wassenaar

If you're not going to do any calculations in the script that depend on the load order then I recommend not reordering the table. In the charts you can specify the sorting regardless of the load order. It will save you a potential costly extra load of the joined table to sort the data.

The max date can be found with Max([TPR DATE]) and the fee amount of that date with FirstSortedValue([FEE AMOUNT], -[TPR DATE]).


talk is cheap, supply exceeds demand
Anonymous
Not applicable
Author

hi,

maybe try:

left join (bde_1)

LOAD

    [ ID NUMBER],

    [FEE AMOUNT],

    [TPR_DATE]

from $(qvd_layer_2_path)T_REG.qvd(qvd)

where [FEE AMOUNT] >0;

left join (bde_1)

LOAD

    [ ID NUMBER],

   max( [TPR_DATE]) as MaxDate

from $(qvd_layer_2_path)T_REG.qvd(qvd)

Group by [ID NUMBER];

Kushal_Chawda

may be like this

left join (bde_1)

LOAD

    [ ID NUMBER],

    [FEE AMOUNT],

    [TPR_DATE]

from $(qvd_layer_2_path)T_REG.qvd(qvd)

where [FEE AMOUNT] >0;

inner join (bde_1)

LOAD

    [ ID NUMBER],

   max( [TPR_DATE]) as [TPR_DATE]

Resident bde_1

group by [ ID NUMBER];