Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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.
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);
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...
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