Help me to derive new field in one table using the existing field in other table.

[govind1501]

Hi All, Can you please help us on below requirement.

I have two tables Customer and Priority.

Customer table is the master table, now i need to get the new fields/columns (High and Low) in the customer table using the existing field/column 'Priority' in the priority table. Also  we need to make sure to not increase the row count in customer table as it is the master/fact table in data model. Please find sample data and expected output below and advise me the approach please.

Customer Table:

Emp	Country	Status
101	India	Open
102	Srilanka	Closed
103	Australia	Open
104	England	Closed
105	England	Open
106	India	Closed
107	Bangladesh	Open

Priority Table:

Emp	Country	Priority
101	India	High
101	India	Low
102	Srilanka	Low
103	Australia	High
103	Australia	Low
104	England	Low
105	England	High

Expected output:

Emp	Country	Status	High	Low
101	India	Open	Y	N
102	Srilanka	Closed	N	Y
103	Australia	Open	Y	Y
104	England	Closed	Y	Y
105	England	Open	Y	N
106	India	Closed	N/A	N/A
107	Bangladesh	Open	N/A	N/A

Thanks

[Taoufiq_Zarra · Accepted solution]

Hi,

I propose you this solution

Attached the Qlikview File

the Script:

Data:

load * inline [
Emp,Country,Status
101,India,Open
102,Srilanka,Closed
103,Australia,Open
104,England,Closed
105,England,Open
106,India,Closed
107,Bangladesh,Open
];

join

load * inline [
Emp,CountryN,Priority
101,India,High
101,India,Low
102,Srilanka,Low
103,Australia,High
103,Australia,Low
104,England,Low
105,England,High
];

Hight:

load Emp,Country,Status,'Y' as High resident Data where Priority='High';

join

load Emp,Country,Status,'Y' as Low resident Data where Priority='Low';
join
load Emp,Country,Status,'N/A' as High,'N/A' as Low resident Data where len(Priority)=0;

drop table Data;

and the output :

the null values correspond to the values not filled.

for example 105 England High exist and 105 England Low does not exist in the source table.

[Kushal_Chawda]

try with applymap approach instead of join

High_Map:
mapping LOAD Distinct
    Emp&Country as Key,
    'Y' as High
FROM [lib://Qlik web]
(html, utf8, embedded labels, table is @2)
where Priority='High';

Low_Map:
mapping LOAD Distinct
    Emp&Country as Key,
    'Y' as Low
FROM [lib://Qlik web]
(html, utf8, embedded labels, table is @2)
where Priority='Low';

T1:
Load Emp,
     Country,
     Status,
     if(match(Low,'Y','N') and High='NA','N',High) as High,
     if(match(High,'Y','N') and Low='NA','N',Low) as Low;
LOAD
    Emp,
    Country,
    ApplyMap('High_Map',Emp&Country,'NA') as High,
    ApplyMap('Low_Map',Emp&Country,'NA') as Low,    
    Status
FROM [lib://Qlik web]
(html, utf8, embedded labels, table is @1);

[govind1501]

Both the solutions are working. Thank you MVP for providing optimized solution... Currently i am using joins in first place and will implement your solution in optimization... 

[Kushal_Chawda]

You should avoid using joins and always implement optimised solution. But it's up to you.

https://community.qlik.com/t5/Qlik-Design-Blog/Don-t-join-use-Applymap-instead/ba-p/1467592