Storing loaded data in a variable.

[#1]

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

[#2 · Accepted solution]

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.

[johnw]

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.