Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am pretty new to Qlik I am attempting to use Apply Maps and an IF Statement for the following issue:
I have 3 tables of data
(Fact) Table - Table A - churnID, Client ID, Indicator
(Lookup) Table B -churnID, Description ID, Description
Table C - Description ID, Description
Only Table A & B have the same churn ID(field) so the lookup is easy, however in table A there is an 'Indicator' field IF a '2' then I should lookup and return the Description field in Table C, IF the indicator is NULL then by default it should return the description in Table B?
Please help 🙂
@LB187 Could you please provide Samples from all the 3 tables and also the expected output so that we could you help you to resolve this issue.
You need multiple and partly nested mappings, maybe something like this:
MapDescriptionB:
mapping load [Description ID], Description from B;
MapDescriptionC:
mapping load [Description ID], Description from C;
MapDescriptionID:
mapping load churnID, [Description ID] from B;
Final:
load *,
if(Indicator = 'a',
applymap('MapDescriptionC', applymap('MapDescriptionID', churnID, null()), 'no match C'),
applymap('MapDescriptionB', [Description ID], 'no match B')) as Description
from A;
- Marcus
Try this way , bit lengthy but will do the job for you
T3:
MAPPING Load * inline [
DescriptionID, Description
A1,AAA
A2,BBB
A3,CCC
A4,DDD
];
T2:
LOAD *,
ApplyMap('T3',[DescriptionID]) AS Description_C;
Load * inline [
churnID, DescriptionID, Description
1,A1,AAA
2,A2,BBB
3,A3,ABCE
4,A4,FFF
];
T1:
Load *,
If(len(Indicator) <> '0',
Lookup('Description_C',[churnID],[churnID],'T2'),
Lookup('Description',[churnID],[churnID],'T2')) as Description;
Load * inline [
churnID, Client ID, Indicator
1,1,1
2,2,2
3,3,
4,4,4
];
drop table T2;