Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

paulelf925
New Contributor

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:

   

DateNameColourSizeQuantityScore
01/04/2018FredGreen1cm502
01/04/2018JulieRed2cm1003
01/04/2018PeterRed3cm756
01/04/2018JohnBlue1cm252
01/04/2018SamGreen3cm508
01/04/2018PeterBlue1cm1002
02/04/2018JohnGreen2cm755
02/04/2018SamBlue3cm257
02/04/2018FredGreen1cm1002
02/04/2018JulieRed2cm753
02/04/2018PeterGreen3cm258
02/04/2018PeterRed1cm501
02/04/2018JohnGreen3cm1008
02/04/2018SamGreen1cm1002
02/04/2018FredRed2cm753
02/04/2018JulieGreen3cm258
03/04/2018JohnBlue1cm1002
03/04/2018SamRed1cm751
03/04/2018PeterBlue2cm1004
03/04/2018JohnBlue2cm754
03/04/2018SamRed1cm251
03/04/2018FredBlue3cm507
03/04/2018JulieGreen1cm1002
03/04/2018PeterBlue1cm752

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:

ColourSizeScore
Red1cm1
Red2cm3
Red3cm6
Blue1cm2
Blue2cm4
Blue3cm7
Green1cm2
Green2cm5
Green3cm8

Can anybody please help with this?


Thank in advance!

4 Replies
chinnuchinni
Contributor III

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

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
Honored Contributor II

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...

i think very helpful for this use case.

sasiparupudi1
Honored Contributor III

Re: Looking up data from another table

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

];

Community Browser