Skip to main content
Woohoo! Qlik Community has won “Best in Class Community” in the 2024 Khoros Kudos awards!
Announcements
Save $600 on Qlik Connect registration! Sign up by Dec. 6 to get an extra $100 off with code CYBERSAVE: REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Load modifying data from previous table

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!

1 Solution

Accepted Solutions
maxgro
MVP
MVP

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;

View solution in original post

3 Replies
maxgro
MVP
MVP

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;

maxgro
MVP
MVP

see attachment

Not applicable
Author

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