topic Update field if value matches/exists in other table in QlikView

Update field if value matches/exists in other table

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

Hi masters

I have the following scenario.

Map_Program:

Mapping load * inline [

Program_Code, Program

104, XXXX

111, YYYY

129, ZZZZ

175, WWW

];

Table1:

Load * INLINE [

Location, Segment

AAAA, Direct

BBBB, Direct

CCCC, Direct

DDDD, Direct

EEEE, Direct

FFFF, Direct

];

Table2:

Load Account,

Customer,

Program_Name,

ApplyMap('Map_Program',Program_Name,'Other') as Program,

Period,

Location,

Amount

Resident DWH;

For the remaining "Other" resulting from the ApplyMap above, I would like to update the Program column (='Direct') if location from Table2 = location from Table1.

How can I do in the load statement?

Thanks

Re: Update field if value matches/exists in other table

sunny_talwar — Mon, 11 Sep 2017 19:05:42 GMT

May be this

Map_Program:

Mapping load * inline [

Program_Code, Program

104, XXXX

111, YYYY

129, ZZZZ

175, WWW

];

Table1:

Load * INLINE [

Location, Segment

AAAA, Direct

BBBB, Direct

CCCC, Direct

DDDD, Direct

EEEE, Direct

FFFF, Direct

];

Table2:

Load Account,

Customer,

Program_Name,

ApplyMap('Map_Program',Program_Name, If(Exists(Location), 'Direct', 'Other')) as Program,

Period,

Location,

Amount

Resident DWH;

Re: Update field if value matches/exists in other table

sunny_talwar — Mon, 11 Sep 2017 19:06:32 GMT

Or may be this

Map_Program:

Mapping load * inline [

Program_Code, Program

104, XXXX

111, YYYY

129, ZZZZ

175, WWW

];

Table1:

Load * INLINE [

Location_Name, Segment

AAAA, Direct

BBBB, Direct

CCCC, Direct

DDDD, Direct

EEEE, Direct

FFFF, Direct

];

Table2:

Load Account,

Customer,

Program_Name,

ApplyMap('Map_Program',Program_Name, If(Exists(Location_Name, Location), 'Direct', 'Other')) as Program,

Period,

Location,

Amount

Resident DWH;

Re: Update field if value matches/exists in other table

its_anandrjs — Mon, 11 Sep 2017 19:18:36 GMT

Few questions

1. In Program_Name what is the data it is code or Program name

2. After apply mapping Programe name = location if it is then try this

Table1:

Mapping

Load * INLINE [

Location, Segment

AAAA, Direct

BBBB, Direct

CCCC, Direct

DDDD, Direct

EEEE, Direct

FFFF, Direct

];

ApplyMap('Map_Program',Program_Name,ApplyMap('Table1',Location)) as Program,

Re: Update field if value matches/exists in other table

felipe_oliveira — Mon, 11 Sep 2017 21:03:52 GMT

Thanks Sunny! It worked perfectly!