Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Not applicable

Getting variable from previous load to determine range of next load

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

1 Solution

Accepted Solutions
MVP
MVP

Getting variable from previous load to determine range of next load

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

5 Replies
MVP
MVP

Getting variable from previous load to determine range of next load

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

MVP
MVP

Getting variable from previous load to determine range of next load

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.

MVP
MVP

Getting variable from previous load to determine range of next load

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.

Not applicable

Getting variable from previous load to determine range of next load

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

MVP
MVP

Getting variable from previous load to determine range of next load

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