Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello
With Applymap, if the mappings are not found, is there any way we could then get it to look at a different list. We have a column in our qlikview table which maps to a column in excel file and gets the value from the second colum in excel to show in Qlikview.
If for someone reason, the mapping is not found, could we get Qlikview to look at a different list? This will mean the column in the data model will show the value either from the first or second list.
Sorry if it's confusing but I couldn't explain it much easier.
Thanks
Like this?
Cust1:
Mapping Load * Inline
[
Cust, Country
A, UK
B, Germany
C, France
H, Poland
];
Cust2:
Mapping Load * Inline
[
CustID, Country
D, Spain
E, Ukraine
I, Hungary
];
Load
*,
ApplyMap('Cust1',Customer, ApplyMap('Cust2',Customer,'Unknown')) as Country
Inline
[
Customer, Sales
A, 100
B, 120
C, 80
D, 400
E, 500
F, 125
];
Yes, with applymap function, you can write a third parameter to specify the value if the applymap doesn't meet value.
applymap('mapname', expr [ , defaultexpr ] )
For example, tou can specify another applymap as third parameter inside the applymap function
Like this?
Cust1:
Mapping Load * Inline
[
Cust, Country
A, UK
B, Germany
C, France
H, Poland
];
Cust2:
Mapping Load * Inline
[
CustID, Country
D, Spain
E, Ukraine
I, Hungary
];
Load
*,
ApplyMap('Cust1',Customer, ApplyMap('Cust2',Customer,'Unknown')) as Country
Inline
[
Customer, Sales
A, 100
B, 120
C, 80
D, 400
E, 500
F, 125
];
Thanks Yoann although please look at my response to Manish's solution.
Thanks Manish. Your solution is close to what I'd like to get to, although in second applymap I have a different field to map to. If I take your example, my first applymap is mapping on Customer. If no customers are found, my second applymap is mapping on a different field within the qlikview table with similar values (Country)
Cust1:
Mapping Load * Inline
[
Cust, Country
A, UK
B, Germany
C, France
H, Poland
];
Cust2:
Mapping Load * Inline
[
CustID, Country
DD, Spain
EE, Ukraine
II, Hungary
];
Load
*,
ApplyMap('Cust1',Customer, ApplyMap('Cust2',ID,'Unknown')) as Country
Inline
[
Customer, Sales, ID
A, 100, AA
B, 120, BB
C, 80, CC
D, 400, DD
E, 500, EE
F, 125, FF
];
I think It will work.
ApplyMap('Cust1',Customer, ApplyMap('Cust2',Country,'Unknown')) as Country
Thanks Yoann very much. Yes it works.
Thanks Manish for your help. Your both solution works. Great.