Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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.
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
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;
Hi Michael, There is common key in the join between tables.
Hi dd ddd, Just try the Incremental load like Nicole suggested
Yeah you are correct .
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.
Yes. First you need to load the new & updated data after that load from qvd with Exists clause.
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