topic Re: max value, how to load it in QlikView

max value, how to load it

Sun, 27 Sep 2015 00:27:13 GMT

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

Re: max value, how to load it

MarcoWedel — Sun, 27 Sep 2015 06:48:02 GMT

What is your expected result in bde_1?

regards

Marco

Re: max value, how to load it

Gysbert_Wassenaar — Sun, 27 Sep 2015 06:54:40 GMT

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

Re: max value, how to load it

Anonymous — Sun, 27 Sep 2015 07:43:48 GMT

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

Re: max value, how to load it

Kushal_Chawda — Sun, 27 Sep 2015 10:14:25 GMT

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