topic excel in QlikView

excel

raadwiptec — Wed, 25 Nov 2020 16:16:04 GMT

My source data is an excel file

Invoice ! Year ! Quantity | Price

100 | 2017 | 10 | 25

100 | 2017 | 09 | 35

100 | 2017 | 08 | 35

every month the same excel file gets changed with different prices. so when i run the qlikview file again it should be saved or concatenated in QVD. But now it over rights . any sugg

Re: excel

jaumecf23 — Tue, 27 Jun 2017 14:41:04 GMT

The first step that you should do is to create a file that only has to be executed when new data is available. This file has to create a QVD like Extraction_Excel_Month_Year.qvd (Example: Extraction_Excel_June_2017.qvd ). This file will generate a new QVD each month with only the data of that month.

Then you need to create another step that will read all these QVDs, something like that :

FROM path\Extraction_Excel_*.qvd.

Then you will have all the information hosted in the different QVDs in one table.

Re: excel

Anonymous — Tue, 27 Jun 2017 14:52:50 GMT

Hi there,

When saving to a QVD, it first deletes the QVD and then replaces it with whatever data is there. So if the data is changing then the QVD will replicate those changes.

I would suggest organizing your spreadsheet to allow for concatenation. If someone is going to update the price, a new line should be entered with a timestamp of price change, for example your columns should be:

Having an ID is not needed but it is going for avoiding duplicates.

Is this something you can do or do you not have control over this process?

let me know

Re: excel

Anonymous — Tue, 27 Jun 2017 15:02:01 GMT

Correct me if I'm wrong here Jaume,

But I believe what is being said here is that there are no new lines of data being added. People are going into the excel sheet and changing the original data, so the old data dissapears.

Re: excel

raadwiptec — Wed, 28 Jun 2017 09:37:00 GMT

no basically the user updates with more than 1000 lines.. so their is no control over that process. we cannot ask the user to add an extra line for the scripting purpose..

Re: excel

raadwiptec — Wed, 28 Jun 2017 09:40:34 GMT

correct. no new lines added. so we need to have a method to concatenate the changed lines again as new