topic Apply Maps/ IF Statements in QlikView

Apply Maps/ IF Statements

LB187 — Wed, 29 Jun 2022 10:24:36 GMT

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 🙂

Re: Apply Maps/ IF Statements

sidhiq91 — Wed, 06 Jul 2022 05:31:50 GMT

@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.

Re: Apply Maps/ IF Statements

marcus_sommer — Wed, 06 Jul 2022 06:54:33 GMT

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

Re: Apply Maps/ IF Statements

GnaneswarReddy — Wed, 06 Jul 2022 07:19:04 GMT

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;