topic Re: Update field data based upon a date in load script in QlikView

Update field data based upon a date in load script

Tue, 26 Jun 2012 15:05:02 GMT

Hi all,

I've always really hated dealling with dates! but now I need to bite the bullet.

I have a table with a number of fields loaded. In the load script I want to change specific records from one value to another based upon the date.

The table has two fields that I want to work with, absolute_time (formated as YYYY-MM-DD 00:00:00 ) and Parameter

What I want to do is....

if Parameter = 'x' and absolute_time is after 1st Jan 2011, then replace Parameter with 'y'.

if Parameter = 'x' and absolute_time is before 1st Jan 2011, then keep Parameter as 'x'

Can somone give me a couple of pointers in how to kick this off?

Thanks

Mark

Re: Update field data based upon a date in load script

Tue, 26 Jun 2012 15:23:45 GMT

Hi Mark,

Can you give a try to this expression

if( parameter = 'X' and date(timestamp)>'01/01/2011' , parameter='Y')

Let me know if it didn't worked 4 you

thanks

Meher

Re: Update field data based upon a date in load script

Tue, 26 Jun 2012 15:36:45 GMT

Hi Meher,

Thanks, I tried that, but the second "parameter" field name in the expression is not recogised (coloured red) in the load script.

Is something special required for the load script?

Thanks

Mark

Re: Update field data based upon a date in load script

christian77 — Tue, 26 Jun 2012 15:59:58 GMT

Hi:

If the tiemstamp in your function is related to today(), use today().

If the timestamp is a value in one of your tables, use the function peek('field',record,'table') (see help) to retrieve that value.

if the timestamp is a calculation from values on a table, i have to think about it, but it is possible. it could be something like load max(date) resident yourtable;

Specify a little more please.

luck.

Re: Update field data based upon a date in load script

Tue, 26 Jun 2012 16:00:17 GMT

Hi Mark,

Can you give a try with variables.

set varA = 'X'

Set varB = 'Y'

if( parameter = $(varA) and date(timestamp)>'01/01/2011' , parameter= $(varB) )

If possible can you upload your script. which clarifies me more

thank you

Meher