Appending data to historic data using SQL selects

Fri, 03 May 2013 11:50:13 GMT

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

Re: Appending data to historic data using SQL selects

er_mohit — Fri, 03 May 2013 12:01:43 GMT

see here

http://community.qlik.com/message/313830

See here, here and here for more information.

http://community.qlik.com/thread/53555

and this one

Rob's blog post on incremental loading

Re: Appending data to historic data using SQL selects

Thu, 16 May 2013 11:52:11 GMT

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

topic Re: Appending data to historic data using SQL selects in QlikView

Appending data to historic data using SQL selects

Re: Appending data to historic data using SQL selects

Re: Appending data to historic data using SQL selects