topic Getting variable from previous load to determine range of next load in QlikView

Getting variable from previous load to determine range of next load

Fri, 10 Sep 2010 17:02:03 GMT

The task: there's a huge db table which will be loaded into qlikview. To decrease the load time of the following loads I will store the data a qvd file.

When the document is opened next time, I will load the data from the qvd file. Then I would get a max value of the primary key = id. Then I will load additional data from the db table. Just a kind of incremental load.

script must be something like this:

tickets:

LOAD id, ticket FROM ticket.qvd;

GET (???) max(id) as max_id FROM tickets;

SQL SELECT id, ticket FROM ticket_table WHERE id > max_id;

STORE tickets INTO Ticket.QVD;

I hope the explanation is understandable.

I anyone has an idea I would be thankful, best Thomas

Getting variable from previous load to determine range of next load

johnw — Fri, 10 Sep 2010 17:41:05 GMT

Something like this:

Tickets:
LOAD ID, Ticket
FROM ticket.qvd (QVD)
;
MaxIDTable:
LOAD max(ID) as MaxIDField
RESIDENT tickets
;
LET MaxIDVariable = peek(MaxIDField)
;
DROP TABLE MaxIDTable
;
CONCATENATE (Tickets)
LOAD *
;
SQL SELECT ID, Ticket
FROM ticket_table
WHERE ID > $(MaxIDVariable)
;
STORE Tickets INTO ticket.qvd (QVD)
;

Getting variable from previous load to determine range of next load

Miguel_Angel_Baeyens — Fri, 10 Sep 2010 17:44:45 GMT

Hello,

I would do as follows

MaxTicketID:LOAD Max(id) as max_id FROM ticket.qvd (qvd);
LET vMaxID = Peek('max_id'); // This will store your maximum id in the variable
DROP TABLE MaxTicketID; // You no longer need this temp table
Tickets:LOAD id, ticket FROM ticket.qvd (qvd);LOAD *;SQL SELECT id, ticket FROM ticket_table WHERE id > $(vMaxID);

Hope this helps!

EDIT: I've just seen John's post after sending. Anyway, I think the work it's worth posting.

Getting variable from previous load to determine range of next load

johnw — Fri, 10 Sep 2010 17:53:41 GMT

The approaches are similar, but they are a little different. I'm not sure which would perform better. Since performance is the goal here, I'd suggest trying both approaches to see which executes more quickly.

Getting variable from previous load to determine range of next load

Mon, 13 Sep 2010 10:34:16 GMT

Hello John, Hello Miguel,

thank so much for your answers. That helps so much.

I always forget, that you'll get an variable value with function "peek". Thats still different to some other programming languages I know.

Thank again.

Thomas

Getting variable from previous load to determine range of next load

johnw — Mon, 13 Sep 2010 17:50:57 GMT

Out of curiousity, DID you performance test both approaches? And if so, which was faster?