Lookup and add more than one column to the main table

[#1]

Hello everyone,

I would like to perform the following in Qlikview but not sure how best to do it. I don't think it's a hard thing to do but I'm just new to qlikview.

I have a table as follows

ID	Criteria	step
2512	cri2	1
2874	cri3	2
3290	cri1	2
1249	cri1	3
2840	cri3	1
1295	cri2	1

Then I want to do a lookup from three other different tables based on the criteria column. If criteria is cri1, cri2 or cri3 I need to use the appropriate table (Cri1 table, Cri2 Table, or Cri3 Table), then do a lookup to find the step row and then add in all columns for that step. So basically the output table will have 5 extra columns for each row. See below that gives an explanation.

cri1, cri2, cri3 tables are as below:

Cri1 table:
step	col1	col2	col3	col4	col5
1	5	6	3	2	6
2	7	8	3	5	7
3	2	4	6	3	9
Cri2 table:
step	col1	col2	col3	col4	col5
1	4	5	3	9	2
2	3	7	2	7	5
3	2	6	8	4	5
Cri3 table:
step	col1	col2	col3	col4	col5
1	7	4	2	7	8
2	8	5	1	8	7
3	6	7	6	3	2

Now based on the above values, the output table should be as below:

ID	Criteria	step	col1	col2	col3	col4	col5
2512	cri2	1	4	5	3	9	2
2874	cri3	2	8	5	1	8	7
3290	cri1	2	7	8	3	5	7
1249	cri1	3	2	4	6	3	9
2840	cri3	1	7	4	2	7	8
1295	cri2	1	4	5	3	9	2

Thanks!

[Anonymous]

Hello,

I think you can do the following:

Final_table:

Load ID, criteria, Step resident table_name where Criteria = 'cri1'

Left Join (Final_table)

Load Step, Col1, col2, col3, col4, col5 resident Cri1 table;

Concatenate

Load ID, criteria, Step resident table_name where Criteria = 'cri2'

Left Join (Final_table)

Load Step, Col1, col2, col3, col4, col5 resident Cri2 table;

concatenate

Load ID, criteria, Step resident table_name where Criteria = 'cri3'

Left Join (Final_table)

Load Step, Col1, col2, col3, col4, col5 resident Cri3 table;

[swuehl · Accepted solution]

You can load your three criteria tables into a single table using the table name as Criteria value, then JOIN it to the first table:

Table:

LOAD ID, Criteria, step

FROM Yourfirsttable;

TMP:

LOAD 'cri1' as Criteria, step, col1, col2, col3, col4, col5

FROM YourCriteria1Table;

LOAD 'cri2' as Criteria, step, col1, col2, col3, col4, col5

FROM YourCriteria2Table;

LOAD 'cri3' as Criteria, step, col1, col2, col3, col4, col5

FROM YourCriteria3Table;

LEFT JOIN (Table)

LOAD * RESIDENT TMP;

DROP TABLE TMP;

[Gysbert_Wassenaar]

Something like this:

Result:

LOAD 'cri1' as Criteria, step, col1, col2, col3, col4, col5 FROM Cri1Table;

CONCATENATE (Result)

LOAD 'cri2' as Criteria, step, col1, col2, col3, col4, col5 FROM Cri2Table;

CONCATENATE (Result)

LOAD 'cri3' as Criteria, step, col1, col2, col3, col4, col5 FROM Cri3Table;

RIGHT JOIN (Result)

LOAD ID, Criteria, step FROM TableAsFollows;

[#5]

but I need to tell qlik that for each record in first table, it needs to check criteria and if criteria is cri1, then look at table cri1. In the script above, you're saying I add a Criteria column to my cri tables too?

[Kushal_Chawda]

Another option

Data:
LOAD ID,
lower(Criteria) as Criteria,
autonumber(step&lower(Criteria)) as Key,
step
FROM
[https://community.qlik.com/thread/219686]
(html, codepage is 1252, embedded labels, table is @1);

Left Join(Data)
LOAD *,autonumber(step & 'cri1') as Key Inline [
step col1 col2 col3 col4 col5
1,  5    6    3    2    6
2    7    8    3    5    7
3    2    4    6    3    9 ](delimiter is '');

Left Join(Data)
LOAD *,autonumber(step & 'cri2') as Key Inline [
step col1_1    col2_1    col3_1    col4_1    col5_1
1    4    5    3    9    2
2    3    7    2    7    5
3    2    6    8    4    5 ](delimiter is '');

Left Join(Data)
LOAD *,autonumber(step & 'cri3') as Key Inline [
step col1_2    col2_2    col3_2    col4_2    col5_2
1    7    4    2    7    8
2    8    5    1    8    7
3    6    7    6    3    2 ](delimiter is '');

Final:
NoConcatenate
LOAD ID,
Criteria,
step,
alt(col1,col1_1,col1_2) as col1,
alt(col2,col2_1,col2_2) as col2,
alt(col3,col3_1,col3_2) as col3,
alt(col4,col4_1,col4_2) as col4,
alt(col5,col5_1,col5_2) as col5
Resident Data;

DROP Table Data;

[swuehl]

QlikNewbie k wrote:

but I need to tell qlik that for each record in first table, it needs to check criteria and if criteria is cri1, then look at table cri1. In the script above, you're saying I add a Criteria column to my cri tables too?

Exactely. Add a constant value per criteria table as key for the JOIN.

[#8]

Thanks, so will QlikView then do the join based on both Criteria and step?

[swuehl]

Yes, this is how it should work, join the tables based on common field names and matching field values.

[#10]

This works, awesome!!