Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
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

20 Replies
userid128223
Creator
Creator
Author

Hi Sunil Kumar

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

sunilkumarqv
Specialist II
Specialist II

  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 .

userid128223
Creator
Creator
Author

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);

Not applicable

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 ;

userid128223
Creator
Creator
Author

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);

Not applicable

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.

Nicole-Smith

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;

userid128223
Creator
Creator
Author

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.

Nicole-Smith

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.

userid128223
Creator
Creator
Author

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.