topic Re: update qvd and append in QlikView

update qvd and append

userid128223 — Fri, 24 Jan 2014 21:37:59 GMT

Hi Everyone.

I have a qvd with following type of records.

ID Name Amount

123 james $300

127 John $234

547 Alex $555

New file from sales comes in daily basis with new and corrected records. Therefore I want to update any corrected records and append new records in above qvd.

for example today's sales file contains

ID Name Amount

123 James $265

432 Judy $800

How do you handle above scenerio. Please share example qvw.

thanks

Re: update qvd and append

Nicole-Smith — Fri, 24 Jan 2014 21:46:58 GMT

Load script that looks like the following should do the trick (first load in your new data, then the qvd--the where not exists phrase is what is going to give you what you need):

Data:

LOAD ID,

     Name,

     Amount

FROM NewData.csv (txt, codepage is 1252, embedded labels, delimiter is ',', msq);

CONCATENATE (Data)

LOAD ID,

     Name,

     Amount

FROM QVD.qvd (qvd)

WHERE not exists(ID);

STORE * FROM Data into QVD.qvd;

Example files are also attached.

Re: update qvd and append

Fri, 24 Jan 2014 21:58:28 GMT

Hi,

Cases like these are done through incremental loads. They are very handy. Check out the cookbook by Rob Wunderlich its amazing and has all the tools you need. It has a working qvw with clear comments that could be used for appending, updating and deleting records.

Downloads - Rob Wunderlich Qlikview Consulting

Thanks

Re: update qvd and append

Anonymous — Fri, 24 Jan 2014 22:15:00 GMT

Edit: Ignore my solution below. I interpreted Nicole's solution wrong.

TempData:

LOAD ID,

Name,

Amount

FROM QVD.qvd (qvd);

LEFT JOIN

LOAD ID,

Name as NewName,

Amount as NewAmount

FROM NewData.csv (txt, codepage is 1252, embedded labels, delimiter is ',', msq);

//This is to update the data

Data:

LOAD ID,

IF(Len(NewName>0),NewName,Name) AS Name,

IF(Len(NewAmount>0),NewAmount,Amount) AS Amount

RESIDENT TempData;

DROP TABLE TempData;

Re: update qvd and append

Fri, 24 Jan 2014 23:16:17 GMT

Hi Michael, There is common key in the join between tables.

Re: update qvd and append

Fri, 24 Jan 2014 23:17:12 GMT

Hi dd ddd, Just try the Incremental load like Nicole suggested

Re: update qvd and append

Anonymous — Sat, 25 Jan 2014 00:12:37 GMT

Yeah you are correct .

Re: update qvd and append

userid128223 — Sat, 25 Jan 2014 01:45:45 GMT

thanks for the solution. so the order of which file goes first matters. new data is read first and then old data is concatenated and records that matches are purged out based on where exist clause. am i correct.

Re: update qvd and append

Sat, 25 Jan 2014 03:24:22 GMT

Yes. First you need to load the new & updated data after that load from qvd with Exists clause.

Re: update qvd and append

sunilkumarqv — Sat, 25 Jan 2014 05:24:16 GMT

Try this and add one more column LastUpdateDate , Do increamental Load.

//Initial Reload & generate Qvd

ProdMaster:

LOAD ID,

Name,

Amount

LastUpdateDate

FROM

Sample_Data.xls

(biff, embedded labels, table is [Sample_Data$]);

Store ProdMaster into [..\Data\Qvds\ProdMaster.Qvd];

DROP Table ProdMaster;

//exit script;

Max_LastUpdateDate:

LOAD

Max(ID) As Max_ID

FROM

[..\Data\Qvds\ProdMaster.Qvd]

(qvd);

//exit script;

Let vMaxID = peek('Max_ID',0,'Max_LastUpdateDate');

Drop Table Max_LastUpdateDate;

Complete_Data:

LOAD ID,

Name,

Amount

LastUpdateDate

FROM

[..\Data\Qvds\ProdMaster.Qvd]

(qvd);

Concatenate

LOAD ID,

Name,

Amount

LastUpdateDate

FROM

Sample_Data.xls

(biff, embedded labels, table is [Sample_Data$])

where ID>'$(vMaxID)';

Store Complete_Data into [..\Data\Qvds\ProdMaster.Qvd];

Regards

Sunil

Re: update qvd and append

userid128223 — Sat, 25 Jan 2014 12:23:28 GMT

Hi Sunil Kumar

How is your solution better or different then one suggested by Nicole. Please explain.

Re: update qvd and append

sunilkumarqv — Mon, 27 Jan 2014 06:07:15 GMT

my solution works only when you want to append the data not to udate records,Please ignore my solution best use is where not clause Exist is the best solution .

Re: update qvd and append

userid128223 — Sat, 01 Feb 2014 01:06:39 GMT

I have follow-up question. what if there is more then 1 uniq key. I might have to create a composite key. how would I use composite key to do append

WHERE not exists(Should I put composite key here);

another question.

i get error when i do following: Field Not found

empData:

LOAD ID,

Name,

Amount,

LOADID & | & Name as uniqkey

FROM QVD.qvd (qvd);

Re: update qvd and append

Sat, 01 Feb 2014 01:46:05 GMT

Hi Try something like below:

First check the QVD exists, If QVD exists, do Incremental Load else full load from file.

LET vQVDExists = IF(FILESIZE('qvdata\QVD.qvd')>0 , -1 , 0) ;

IF $(vQVDExists) THEN //QVD Exists do Inc Load

TABLEA:

LOAD * , LOADID&'|'&NAME AS UNIQUEKEY FROM SOURCEEXCELFILE ; // SorceFile

LOAD * FROM [qvdata\QVD.qvd] (qvd) WHERE Not Exists(UNIQUEKEY) ; // QVD

ELSE //QVD does not Exists do Full Load

TABLEA:

LOAD * , LOADID&'|'&NAME AS UNIQUEKEY FROM SOURCEEXCELFILE;

ENDIF

STORE TABLEA INTO [qvdata\QVD.qvd] (qvd) ;

DROP Table TABLEA ;

Re: update qvd and append

userid128223 — Sat, 01 Feb 2014 02:11:41 GMT

Hi Dathu

1) I dont need to check for file exist or not

2) your code does not have any concatenate to update and append.

Nicole solution worked for me however I require further modification.

1) I will require composite key to be used. question is can i used composite key in where not exist.

2) I was having issue with field not found. with following code.

empData:

LOAD ID,

Name,

Amount,

LOADID & | & Name as uniqkey

FROM QVD.qvd (qvd);

Re: update qvd and append

Sat, 01 Feb 2014 19:05:12 GMT

Hi , If the two tables have same structure (same field names) then table will be concatenate automatically.

So in my Example, eventhogh, I didn't mention the concatenate, the table will be concatenate because both tables have the same structure.

In your case, Please try like below:

empData:

LOAD ID,

Name,

Amount,

ID & '|' & Name as uniqkey

FROM QVD.qvd (qvd);

I believe, you are making composite key on ID & Name, then above script will work.

Re: Re: update qvd and append

Nicole-Smith — Mon, 03 Feb 2014 12:47:33 GMT

You would do a composite key with a where not exists clause like below. I am assuming your composite key is made up of ID and Name, so you may need to change the fields. I have edited the original load script that I have given you.

Data:

LOAD ID & '|' & Name as CompositeKey,

    ID,

    Name,

    Amount

FROM NewData.csv (txt, codepage is 1252, embedded labels, delimiter is ',', msq);

CONCATENATE (Data)

LOAD ID & '|' & Name as CompositeKey,

    ID,

    Name,

    Amount

FROM QVD.qvd (qvd)

WHERE not exists(CompositeKey, ID & '|' & Name);

STORE * FROM Data into QVD.qvd;

Re: update qvd and append

userid128223 — Mon, 03 Feb 2014 14:30:00 GMT

Thanks Nicole.

I moved away from your solution since I was not getting result due to composite key issue however I will give it a try again. this method if it works then better because i don't have to create separate qvd with key to join later.

Another point is that my composite key will be made of 3 fields ID&Name&StoreNo another field I have in the script.

Can you please explain what is the difference between.

WHERE not exists(ID);

WHERE not exists(CompositeKey, ID & '|' & Name);

How does program internally interpret above 2 statement.

Re: update qvd and append

Nicole-Smith — Mon, 03 Feb 2014 15:10:16 GMT

WHERE not exists(ID) looks where the field ID was loaded in a previous table and only loads data in the current table (with the where clause) where the ID is not in the previously loaded ID field.

WHERE not exists(CompositeKey, ID & '|' & Name) does the same thing, we just have to write it a bit differently. CompositeKey was loaded in a previous table, and we are just creating the composite key in this table, so we need to use ID & '|' & Name instead (since that is what the composite key is made up of--in my example anyway).

If you look up exists() in QV help, it gives a similar example using Employee and FirstName & ' ' & LastName.

Re: update qvd and append

userid128223 — Wed, 12 Feb 2014 21:00:27 GMT

Quick question.

How do you handle records if 2 entry exist for same record and you need to take record with most recent update via update_date.