Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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)
;
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)
;
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.
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.
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
Out of curiousity, DID you performance test both approaches? And if so, which was faster?