topic Edit data based on other fields in QlikView

Edit data based on other fields

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

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!

Re: Edit data based on other fields

chrismarlow — Wed, 07 Nov 2018 15:49:45 GMT

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.

Re: Edit data based on other fields

sunny_talwar — Wed, 07 Nov 2018 15:59:40 GMT

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;

Re: Edit data based on other fields

ahmed_hassan — Tue, 27 Nov 2018 07:26:16 GMT

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
];