Looking up data from another table

[paulelf925]

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

Can anybody please help with this?

Thank in advance!

[chinnuchinni]

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 ?

[sunny_talwar]

May be using ApplyMap and Mapping load

MappingTable:

Mapping

LOAD Colour&'|'&Size,

Score;

LOAD * INLINE [

    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:

LOAD *,

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

LOAD * INLINE [

    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

];

[agigliotti]

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

i think very helpful for this use case.

[sasiparupudi1]

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

MainTable:

LOAD * INLINE [

    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:

LOAD * INLINE [

    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

];