Edit data based on other fields

[ahmed_hassan]

Dear all,

I would like to edit the column "Country" based on "Country Source" and "ID Source".

In other words, I want that qlikview checks "Country Source" and "ID Source" and in case there are values there, It should edit "Country" column and changes ( CN --> DE  &  DE--> US). In order to get the following table:

Any idea how can I do it?  Thanks in  advance!

[chrismarlow · Accepted solution]

Hi Ahmed,

You 'edit' data like this by creating new columns, in this case by joining & then using the new value where it links (unlike SQL/Access where you would update). So assuming your table is called data, the following would do it;

Left join (data)
Load
ID AS [ID Source],
Customer as [Country Source],
Country as Country_New
Resident data;

data_final:
NoConcatenate
Load
ID,
Customer,
If(IsNull(Country_New),Country,Country_New) as Country
Resident data;

Drop table data;

Regards,

Chris.

[sunny_talwar · Accepted solution]

Try this

Table:
LOAD * INLINE [
ID, Customer, Country, Country Source, ID Source
1, A, CN
2, B, DE
3, C, US
4, D, CN, B, 2
5, A, DE, C, 3
];

Left Join (Table)
LOAD Customer as [Country Source],
ID as [ID Source],
Country as Country_New
Resident Table;

FinalTable:
LOAD ID,
Customer,
If(Len(Trim(Country_New)) > 0, Country_New, Country) as Country
Resident Table;

DROP Table Table;

[ahmed_hassan]

Thanks guys, do you have any idea what could i do if my table is as following:

Table:
LOAD * INLINE [
ID, Customer, Country, Country Source, ID Source
1, A, CN, NULL, 0
2, B, DE,0,00
3, C, US,00,0
4, D, CN, B, 2
5, A, DE, C, 3
];