lookup field value based on multiple criteria

qliksensedlin — Sat, 16 Nov 2024 01:25:53 GMT

Hi,

I'm trying to implement something like a multi-column vlookup in Excel for my Qlik table. Here is the simplified scenario:

Column A	Column B	Column C
1	5	A
1	6	B
2	7	C
2	8	D

Criteria 1	Criteria 2	Target Column
1	4	?
2	5	?
3	6	?

I need to fill in my target column by values from Column C based on the combination of values from criteria 1 and criteria 2 columns. Criteria 1 and Criteria 2 correspond to my Column A and Column B in the fact table loaded.

I have tried with FieldValue function with FieldIndex to lookup Column C like this:

=FieldValue('Column C', FieldIndex('Column A', 1))

However, this works only if I have a single criterion to look up. How can I then look up Column C based on multiple criteria in both Column A and Column B?

In addition, how can I refer to the values of Column B and C in my expression? i.e.

=FieldValue('Column C', FieldIndex('Column A', Column B)) is not working

I'm looking for a way to solve this by chart function because I don't want to modify the existing data model.

Thank you.

Re: lookup field value based on multiple criteria

marcus_sommer — Mon, 21 Sep 2020 08:25:13 GMT

You could use a mapping for it, maybe something like this:

m: mapping load [Column A] & '|' & [Column B], [Column C] from t1;

load *, applymap('m', [Criteria 1] & '|' & [Criteria 2], 'default value') as Target from t2;

- Marcus

Re: lookup field value based on multiple criteria

qliksensedlin — Mon, 21 Sep 2020 08:35:27 GMT

Thanks Marcus.

I would prefer a chart solution instead of a script solution. Applymap may work, but not in my use case.

topic Re: lookup field value based on multiple criteria in App Development

lookup field value based on multiple criteria

Re: lookup field value based on multiple criteria

Re: lookup field value based on multiple criteria