Updating the qvd file with csv file base on some field.

[#1]

Hi

       My scenario  is like that :

Qvd file having these followingdata:

                                       Source,    date     , customer,  area, count, revenue

                                       Abc,   24-08-88,  xyzzzzzzz,  mnp,  100,   100.30

                                       Qmn,  23-08-88,  xyyyyyz,  mnp,  200,   300.30

                                       Abc,   24-08-88,  xyvvvvvz,  mnp,  300,   300.30

And  csv file having data like this:

                                       Source,    date     , customer,  area, count, revenue

                                       Qmn,   23-08-88, xmmmmz,  mnp,  500,   500.30

                                       Qmn,   23-08-88,  xnnnnnnnz,  mnp,  700,   700.30

I want to update my qvd file with csv file .

Output qvd should be like that:

                                       Source,    date     , customer,  area, count, revenue

                                       Abc,     24-08-88, xyzzzzzzz,   mnp,  100,   100.30

                                       Qmn,   23-08-88,  xmmmmz,  mnp,  500,   700.30

                                      Qmn,   23-08-88, xnnnnnnnz,   mnp,  500,   700.30

                                       Abc,   24-08-88,  xyvvvvvz,  mnp,  300,   300.30

[Miguel_Angel_Baeyens · Accepted solution]

Hi,

I'd do first a load of those fields in the CSV that make a unique key field between the CSV and the QVD, say it's the Source and Date fields according to your example above.

CSVUpdated:
LOAD Source & '/' & Date AS Key
FROM File.csv
WHERE Condition; // condition is what makes every day's reload different, a date, a record number or anything else
// to load only those values in the CSV that have to update the QVD file

Second I'd load the QVD file except for rows with those values

QVDExceptUpdated:
LOAD *
FROM File.qvd (qvd)
WHERE NOT EXISTS(Key, Source & '/' & Date);

Third, concatenate the updated values from CSV.

CONCATENATE (QVDExceptUpdated) LOAD *
FROM File.csv
WHERE EXISTS(Key, Source & '/' & Date);
RENAME TABLE QVDExceptUpdated TO QVDAllUpdated;
STORE QVDAllUpdated INTO File.qvd (qvd);

Hope that helps.

Miguel

[#3]

Hi Miguel

                 Thanks for qick reply. i jst implementd in this way.           

CSVUpdated::

LOAD

     source&'_'&date AS key 

FROM

$(rerun_CSV_PATH)\*_revenue.csv

(txt, codepage is 1252, embedded labels, delimiter is ',', msq)  ;

QVDExceptUpdated:

LOAD *

FROM

D:\qlikViewBackup\qlikview_work\Data\RawQvds\Idea_Rbt_revenue.qvd

(qvd) WHERE NOT EXISTS(Key,source&'_'&date);

But in 2nd step itself i m getting 0 row fetched. Its should fetched 2 rows  like this:

                                    Source,    date     , customer,  area, count, revenue

                                       Abc,     24-08-88, xyzzzzzzz,   mnp,  100,   100.30

                                       Abc,   24-08-88,  xyvvvvvz,  mnp,  300,   300.30

Your logic is correct. I think i implementd in wrong way.Can u explain me in brief plz?

[#4]

Hi

       Sorry Miguel(i did some typing mistake),

         U r correct.  Now i got my desire result.

       Thanks alot.