topic Storing loaded data in a variable. in QlikView

Storing loaded data in a variable.

Wed, 12 May 2010 19:32:36 GMT

Hey Qlikers,

I have an incremental load script that uses the last reaload time to establish what's new in the database. the problem is, my QVS and my database server are in different timezones.

So, what I need to do is determine the reload time based on what time the database thinks it is. I can do that just fine with an oracle query. I'm loading that value into a table in my reload script, but I can't get any of the functions that are supposed to retrieve this data from the table to work properly. In the code included below, all of the statements after the load return set my LastExecTime to <NULL>. I have confirmed by displaying the field on the worksheet after the load script completes, so I know that the data is there.


ReloadTime:
 SQL select sysdate as CurrentTime
 from dual;
LET LastExecTime = Peek('CurrentTime',0,'ReloadTime');
LET LastExecTime = Peek('CurrentTime',-1,'ReloadTime');
LET LastExecTime = FieldValue('CurrentTime',1);
 LET LastExecTime = num(Peek('CurrentTime',0,'ReloadTime'));
 LET LastExecTime = num(Peek('CurrentTime',-1,'ReloadTime'));
 LET LastExecTime = num(FieldValue('CurrentTime',1));

Storing loaded data in a variable.

Wed, 12 May 2010 19:59:13 GMT

YEARGH!

Qlikview aliases all of the fields I attempt to load in a SQL SELECT statement to the same field name, but in all caps. My function calls were returning null because it can't 'CurrentTime', qlikview helpfully changed it to 'CURRENTTIME'.

Argh.

Storing loaded data in a variable.

johnw — Thu, 13 May 2010 00:37:56 GMT

Not sure why it's happening, but it shouldn't happen if you do the rename on the LOAD side instead of the SQL side.

ReloadTime:
LOAD sysdate as CurrentTime
;
SQL SELECT sysdate
FROM dual
;

Though I'm not entirely certain I'm understanding.