Load Script - Select Last Value

[Dayna]

Hello All,

I have a set of data that should ideally only have one record with a reference, but unfortunately for some references I get multiple rows (for different dates, etc etc..).

What I would like to do in the load script is only pull down the last record based on the highest 'TRANSACTION' for each 'REFERENCE' records. I know if I did it in the application I would do a firstsortedvalue() but no idea how to do it in the load script!

Please help!

Kind Regards,

Dayna

[qliksus · Accepted solution]

Hi u can use the Firstsorted value in the script as well

see the attached file

[Dayna]

Hello Qliksus,

Is there any particular reason for doing the firstsortedvalue() in a separate table? As when I do this, it’ll have many fields!!

Finally, what does ‘where Exists(Tran,TRANSACTION)’ do?

Many thanks for your help!

Kind Regards,

Dayna

[qliksus]

Hi Dyana,

Sorry for the late reply i have used Firstsortedvalue in seperate table bcoz u want to take one tansaction for each reference so you have to group by only the referene column u should not include other columns bcoz that wont give you a single value . Thats y i have used that in a seperate table and then the exists command will load only the last Transaction for each reference

[Dayna]

Hello Qliksus,

Many thanks for your response, the original table will have a lot of records – I assume this won’t be a problem?

Regarding the ‘exist’ statement, are you able to drop the firstsortedvalue() table after you’ve done this? Does it work like a join or more like a where-clause to an external table? Sorry, I’ve never seen it used before

Many thanks for your help!

Kind Regards,

Dayna

[Dayna]

Qliksus,

Could you also explain why you use ‘FirstSortedValue(TRANSACTION,-REFERENCE)’ and not ‘FirstSortedValue(REFERENCE,-TRANSACTION)’? I would of always done the latter as it’s the last TRANSACTION number I want for each REFERENCE, so I’m struggling to get my head round it!

Many thanks,

Dayna

[qliksus]

Hi

What u specify in the expression will be returned that is the first column u are giving will be returned based on the

sort weight so if u want to take the last value of the transaction u have to give that in the expression i think i understood it other way around so thats y i have done that way

[qliksus]

you drop the exists table no data will be there i have used this because using a exitsis better than using the keyword  LEFT JOIN  between the tables bcoz try to avoid the join keyword in the script when you go through qlikview help

u will find the below reason 

The explicit Join keyword in QlikView script language performs a full join of the two tables. The result is one table. In many cases such joins will result in very large tables. One of the main features of QlikView is its ability to make associations between tables instead of joining them, which greatly reduces memory usage, increases processing speed and offers enormous flexibility. Explicit joins should therefore be generally avoided in QlikView scripts.

[Dayna]

Great, thank you for your help!