Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
johnw
Champion III
Champion III

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

View solution in original post

5 Replies
johnw
Champion III
Champion III

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

Miguel_Angel_Baeyens

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.

johnw
Champion III
Champion III

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
Author

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

johnw
Champion III
Champion III

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