topic Re: Re: Load modifying data from previous table in QlikView

Load modifying data from previous table

Mon, 04 Aug 2014 14:02:50 GMT

Greetings Community, I am having some trouble doing in the script something a client asked me.

I alredy searched here but i coludn´t find how to do it, if it alredy exists a similar thread i apologice.

I am going to explain with an example so it is easier.

Let´s say i have a table with this data:

ID Field1 Field2 Field3

1 20 12 30

2 1 50 40

3 15 42 11

Then i need to load an Excel file that contains the same fields where the client is going to use it to modify some of the information of the original table. The thing is that it only has to change the fields where information is modified and the field 3 always stay the same.

For example the file would be something like this when the client wants to modify a register (Field 3 doesn´t exist in this file).

ID Field1 Field2

1 35

So the final Table should give this result

ID Field1 Field2 Field3

1 20 35 30

2 1 50 40

3 15 42 11

Notice that the only value that changed is the Field 2 of the ID 1 and the other information remains the same.

How can i make this happen in the script of my qlikview file?

Thanks in advance!

Re: Load modifying data from previous table

maxgro — Mon, 04 Aug 2014 14:14:18 GMT

left join the original table with the excel

and then use a resident load to compare excel fields and original fields

Tmp:

left join (OriginalTable)

load

ID,

field1 as field1excel,

field2 as fieldexcel

from

......excel......

drop table OriginalTable;

Final:

noconcatenate load

ID,

// check if excel fields are modified

if(len(trim(field1excel))>0, field1excel, field1) as field1,

if(len(trim(field2excel))>0, field2excel, field2) as field2,

field3

resident Tmp;

drop table Tmp;

Re: Re: Load modifying data from previous table

maxgro — Mon, 04 Aug 2014 14:22:19 GMT

see attachment

Re: Load modifying data from previous table

Mon, 04 Aug 2014 14:48:24 GMT

Thank you very much Massimo, that worked perfectly and is very simple.