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

Appending data to historic data using SQL selects

Hi,

I read this usage example of how to combine data with historic data to "build up" a dataset from smaller components:

http://www.qvsource.com/wiki/Saving-Historical-Data.ashx

I'm now trying to perform the same using a database table that I load and want to store the new records from but I'm clearly messing up the syntax somehow since I'm getting the following error:

"

Table not found

LOAD * resident NewData

where not exists('SEQID', 'SEQID')

"

Please find the script below:

// create a variable for the QVD name as it will be reused

let alldocuments = 'msg_log.qvd';

// We check the size of the QVD

let size = filesize('$(alldocuments)');

// If the size is greater than zero then we already have (historical) data stored in it

// and so we should load it to the Historic table

// After the first time this script is executed, this should always run

if not isnull(size) then

          Historic:

    LOAD * FROM '$(alldocuments)' (qvd);

end if

// We now make the call to QVSource and get the latest data

// Notice at the end that we're only adding Search_id's to the table that aren't already in it by using the 'where not exist'

OLEDB CONNECT TO [Provider=SQLOLEDB.1;Persist Security Info=True;User ID=user;Initial Catalog=BT;Data Source=DSN;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=workstation;Use Encryption for Data=False;Tag with column collation when possible=False] (XPassword is SDKFJLSKDJEOIFJ);

NewData:

SQL SELECT

     SEQID

    ,STATUS_DATE

    ,START_DATE

    ,END_DATE

    ,STATUS

    ,SENDER

    ,RECEIVER

    ,FORMAT

FROM MSG_LOG;

// Now create a Data -table where we load the previously created two resident tables.

Data:

LOAD * resident Historic;

LOAD * resident NewData

where not exists('SEQID', 'SEQID');

// use where not exists to only load the new records from NewData in addition to the ones previously stored in Historic

// At this point the table includes both historical and the latest data

// We can now store it to the QVD, ready to be reloaded next time the script is run

STORE Data INTO '$(alldocuments)';

2 Replies
Not applicable
Author

Thanks, I ended up looking into QlikView Components project and this had all the tools I needed to easily accomplish what I wanted.