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
Hi Sunil Kumar
How is your solution better or different then one suggested by Nicole. Please explain.
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 .
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);
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 ;
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);
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.
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;
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);
vs
WHERE not exists(CompositeKey, ID & '|' & Name);
How does program internally interpret above 2 statement.
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.
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.