topic Re: Update data in one spreadsheet based off data from another spreadsheet file in QlikView

Update data in one spreadsheet based off data from another spreadsheet file

justcotto — Mon, 25 Feb 2019 19:25:17 GMT

I have an excel file (file1.xlsx) that contains four columns (Term, Definition, Page and Calculation) and another file (file2.xlsx) with three columns (Term, Definition and Calculation). I want the second file to update the first if it encounters a term that is in both. I want it to update the Term, Definition and Calculation of file1.xlsx.

Thank you

Re: Update data in one spreadsheet based off data from another spreadsheet file

rubenmarin — Mon, 25 Feb 2019 20:13:15 GMT

If all terms in file2 exists in file1 you can load file2 first and use exists to load only non-existant terms, ie:

LOAD * From [...file2...](...);
Concatenate LOAD * from [...file1...](...) where not exists(Term);

Re: Update data in one spreadsheet based off data from another spreadsheet file

dplr-rn — Mon, 25 Feb 2019 21:10:13 GMT

Dont understand your problem exactly.

In excel 1 you have

Term, Definition, Page and Calculation

Excel 2 you have

Term, Definition and Calculation

On which column are you matching values between the excels? And which column or columns in excel 1 do you want o update

Re: Update data in one spreadsheet based off data from another spreadsheet file

rwunderlich — Mon, 25 Feb 2019 23:22:50 GMT

A modification to Ruben's answer. You will have to load file1 a second time to get Page values, which I understand are only in File1.

Data:
LOAD * From [...file2...](...);

Concatenate (Data)
LOAD Term, Definition, Calculation
from [...file1...](...) where not exists(Term);

Join (Data)
LOAD Term, Page
from [...file1...](...)

-Rob

Re: Update data in one spreadsheet based off data from another spreadsheet file

Vijesh — Tue, 26 Feb 2019 11:57:56 GMT

Hello Friend,

Please help to solve my query.

https://community.qlik.com/t5/Qlik-Support-Discussions/Last-Cost-and-and-Current-Cost/m-p/1548436#M1315

Re: Update data in one spreadsheet based off data from another spreadsheet file

justcotto — Tue, 26 Feb 2019 19:19:32 GMT

For Excel 1 I would like for the definition and calculation to be updated if a Term is encountered that is in both Excel 1 and 2. Excel 2 has all the correct definitions or standard definitions. Excel 1 may have erroneous data. Excel 2 is used to correct any data that may not be correct in Excel 1. I don't want to bring in all the data from Excel 2, only the corrections.

Re: Update data in one spreadsheet based off data from another spreadsheet file

rwunderlich — Tue, 26 Feb 2019 19:59:21 GMT

TempKey:
LOAD Term as TermFile1
from [...file1...](...);

Data:
LOAD * From [...file2...](...)
where exists(TermFile1, Term);

Concatenate (Data)
LOAD Term, Definition, Calculation
from [...file1...](...) where not exists(Term);

Join (Data)
LOAD Term, Page
from [...file1...](...)

DROP TABLE TempKey;

-Rob