4 Replies Latest reply: Apr 5, 2018 9:59 AM by Sasidhar Parupudi

# Looking up data from another table

Hello, I'm new to Qlik Sense.

I'd like to be able to add a column to my main data table by looking up data from a secondary data table based on 2 criteria columns which I've already associated between the 2 tables.

An example of my main data table can be seen below:

 Date Name Colour Size Quantity Score 01/04/2018 Fred Green 1cm 50 2 01/04/2018 Julie Red 2cm 100 3 01/04/2018 Peter Red 3cm 75 6 01/04/2018 John Blue 1cm 25 2 01/04/2018 Sam Green 3cm 50 8 01/04/2018 Peter Blue 1cm 100 2 02/04/2018 John Green 2cm 75 5 02/04/2018 Sam Blue 3cm 25 7 02/04/2018 Fred Green 1cm 100 2 02/04/2018 Julie Red 2cm 75 3 02/04/2018 Peter Green 3cm 25 8 02/04/2018 Peter Red 1cm 50 1 02/04/2018 John Green 3cm 100 8 02/04/2018 Sam Green 1cm 100 2 02/04/2018 Fred Red 2cm 75 3 02/04/2018 Julie Green 3cm 25 8 03/04/2018 John Blue 1cm 100 2 03/04/2018 Sam Red 1cm 75 1 03/04/2018 Peter Blue 2cm 100 4 03/04/2018 John Blue 2cm 75 4 03/04/2018 Sam Red 1cm 25 1 03/04/2018 Fred Blue 3cm 50 7 03/04/2018 Julie Green 1cm 100 2 03/04/2018 Peter Blue 1cm 75 2

The columns 'Colour' & 'Size' are my 2 criteria columns and the 'score' column is the new column I'd like to add where the value is automatically looked up from the 2nd table as shown below:

 Colour Size Score Red 1cm 1 Red 2cm 3 Red 3cm 6 Blue 1cm 2 Blue 2cm 4 Blue 3cm 7 Green 1cm 2 Green 2cm 5 Green 3cm 8

• ###### Re: Looking up data from another table

you mean that based on second table color & size ,you want to re-write the score in main table ???

can you give us ,,the bit more clarification ??

or else can you provide us the sample o/p ?

• ###### Re: Looking up data from another table

May be using ApplyMap and Mapping load

MappingTable:

Mapping

Score;

Colour, Size, Score

Red, 1cm, 1

Red, 2cm, 3

Red, 3cm, 6

Blue, 1cm, 2

Blue, 2cm, 4

Blue, 3cm, 7

Green, 1cm, 2

Green, 2cm, 5

Green, 3cm, 8

];

Table:

ApplyMap('MappingTable', Colour&'|'&Size, Null()) as Score;

Date, Name, Colour, Size, Quantity

01/04/2018, Fred, Green, 1cm, 50

01/04/2018, Julie, Red, 2cm, 100

01/04/2018, Peter, Red, 3cm, 75

01/04/2018, John, Blue, 1cm, 25

01/04/2018, Sam, Green, 3cm, 50

01/04/2018, Peter, Blue, 1cm, 100

02/04/2018, John, Green, 2cm, 75

02/04/2018, Sam, Blue, 3cm, 25

02/04/2018, Fred, Green, 1cm, 100

02/04/2018, Julie, Red, 2cm, 75

02/04/2018, Peter, Green, 3cm, 25

02/04/2018, Peter, Red, 1cm, 50

02/04/2018, John, Green, 3cm, 100

02/04/2018, Sam, Green, 1cm, 100

02/04/2018, Fred, Red, 2cm, 75

02/04/2018, Julie, Green, 3cm, 25

03/04/2018, John, Blue, 1cm, 100

03/04/2018, Sam, Red, 1cm, 75

03/04/2018, Peter, Blue, 2cm, 100

03/04/2018, John, Blue, 2cm, 75

03/04/2018, Sam, Red, 1cm, 25

03/04/2018, Fred, Blue, 3cm, 50

03/04/2018, Julie, Green, 1cm, 100

03/04/2018, Peter, Blue, 1cm, 75

];

• ###### Re: Looking up data from another table

for your needs I suggest you to take a look at https://help.qlik.com/en-US/sense/February2018/Subsystems/Hub/Content/Scripting/InterRecordFunctions/LookUp.htm?tr=it-IT

i think very helpful for this use case.

• ###### Re: Looking up data from another table

Another option is to simply do a left join to the main table

MainTable:

Date, Name, Colour, Size, Quantity

01/04/2018, Fred, Green, 1cm, 50

01/04/2018, Julie, Red, 2cm, 100

01/04/2018, Peter, Red, 3cm, 75

01/04/2018, John, Blue, 1cm, 25

01/04/2018, Sam, Green, 3cm, 50

01/04/2018, Peter, Blue, 1cm, 100

02/04/2018, John, Green, 2cm, 75

02/04/2018, Sam, Blue, 3cm, 25

02/04/2018, Fred, Green, 1cm, 100

02/04/2018, Julie, Red, 2cm, 75

02/04/2018, Peter, Green, 3cm, 25

02/04/2018, Peter, Red, 1cm, 50

02/04/2018, John, Green, 3cm, 100

02/04/2018, Sam, Green, 1cm, 100

02/04/2018, Fred, Red, 2cm, 75

02/04/2018, Julie, Green, 3cm, 25

03/04/2018, John, Blue, 1cm, 100

03/04/2018, Sam, Red, 1cm, 75

03/04/2018, Peter, Blue, 2cm, 100

03/04/2018, John, Blue, 2cm, 75

03/04/2018, Sam, Red, 1cm, 25

03/04/2018, Fred, Blue, 3cm, 50

03/04/2018, Julie, Green, 1cm, 100

03/04/2018, Peter, Blue, 1cm, 75

];

Left Join(MainTable)

Score:

Colour, Size, Score

Red, 1cm, 1

Red, 2cm, 3

Red, 3cm, 6

Blue, 1cm, 2

Blue, 2cm, 4

Blue, 3cm, 7

Green, 1cm, 2

Green, 2cm, 5

Green, 3cm, 8

];