Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

amiumi66
New Contributor III

update qvd and append

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

1 Solution

Accepted Solutions

Re: update qvd and append

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.

20 Replies

Re: update qvd and append

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.

Not applicable

Re: update qvd and append

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

AJ

michael_gardner
Contributor III

Re: update qvd and append

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;



Not applicable

Re: update qvd and append

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

Not applicable

Re: update qvd and append

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

michael_gardner
Contributor III

Re: update qvd and append

Yeah you are correct .

amiumi66
New Contributor III

Re: update qvd and append

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.

Not applicable

Re: update qvd and append

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

sunilkumarqv
Valued Contributor II

Re: update qvd and append

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

Community Browser