topic Re: Overriding existing Column with new imported table in QlikView

Overriding existing Column with new imported table

aetingu12 — Thu, 05 Jul 2018 13:49:03 GMT

All,

I have a Loader Script as such:

Table_1:

Load ID, CUSTOMER_NAME, REGION

FROM [\\test\data.qvd]

(qvd);

Table_2:

Load ID, PRODUCT

FROM [\\test\data2.qvd]

(qvd);

Which creates the FIELDS ID,CUSTOMER_NAME, REGION and PRODUCT for each ID (So joins the tables)

then I import an excel spreadsheet as:

Excel_1:

Load ID, REGION

FROM [\\test\excel.xlsx]

(ooxml, embedded labels, table is Sheet1)

Excel_1 table has updated REGION codes for select IDs. So I want to be able to do something like this in SQL but in Qlikview:

Case when Excel_1.ID = Table_1.ID then Excel_1.REGION else Table_1.REGION end as REGION.

Is this possible?

Regards,

Aksel

Re: Overriding existing Column with new imported table

neelamsaroha157 — Thu, 05 Jul 2018 13:58:29 GMT

There are multiple ways of doing this, here is one of them-

Left join Excel_1 to Table_1 on ID, rename field REGION to something like New_REGION and then resident the final output table.

Add another field as if(IsNull(new_REGION), REGION, new_REGION) as Final_Region.

Re: Overriding existing Column with new imported table

neelamsaroha157 — Thu, 05 Jul 2018 14:05:06 GMT

Check this out

Re: Overriding existing Column with new imported table

aetingu12 — Thu, 05 Jul 2018 14:14:44 GMT

Apologies, I am on corporate computer which doesn't allow downloading files.

Would it be possible to paste the code?

Re: Overriding existing Column with new imported table

neelamsaroha157 — Thu, 05 Jul 2018 14:53:54 GMT

Table_1:

load * inline [

ID,CUSTOMER_NAME, REGION

2018,A, x

2019,B, y

];

Left Join

Excel_1:

load * inline [

ID, new_REGION

2018,z

];

LOAD *, if(IsNull(new_REGION), REGION, new_REGION) as Final_Region

Resident Table_1;

DROP Table Table_1;

Here is the output:

You can drop the REGION and new_REGION field.

Re: Overriding existing Column with new imported table

aetingu12 — Thu, 05 Jul 2018 15:00:50 GMT

Much appreciated!

Thanks for your patience and help.