max value, how to load it

[#1]

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

[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]).

[Anonymous]

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];