topic Re: Load Script - Select Last Value in QlikView

Load Script - Select Last Value

Dayna — Thu, 21 Jun 2012 13:42:00 GMT

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

Re: Load Script - Select Last Value

qliksus — Fri, 22 Jun 2012 07:31:01 GMT

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

see the attached file

Re: Load Script - Select Last Value

Dayna — Fri, 22 Jun 2012 08:25:16 GMT

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

Re: Load Script - Select Last Value

qliksus — Sat, 23 Jun 2012 08:59:01 GMT

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

Re: Load Script - Select Last Value

Dayna — Mon, 25 Jun 2012 08:08:27 GMT

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

Re: Load Script - Select Last Value

Dayna — Mon, 25 Jun 2012 08:20:28 GMT

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

Re: Load Script - Select Last Value

qliksus — Wed, 27 Jun 2012 05:32:34 GMT

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

Re: Load Script - Select Last Value

qliksus — Wed, 27 Jun 2012 05:44:53 GMT

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.

Re: Load Script - Select Last Value

Dayna — Wed, 27 Jun 2012 15:38:07 GMT

Great, thank you for your help!