Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Live chat with experts, bring your API Integration questions. June 15th, 10 AM ET. REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
userid128223
Creator
Creator

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
Nicole-Smith

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.

View solution in original post

20 Replies
Nicole-Smith

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.

View solution in original post

Not applicable

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
Creator III
Creator III

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

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

Not applicable

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

michael_gardner
Creator III
Creator III

Yeah you are correct .

userid128223
Creator
Creator
Author

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

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

sunilkumarqv
Specialist II
Specialist II

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