Discussion board where members can get started with Qlik Sense.
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:
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:
Can anybody please help with this?
Thank in advance!
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 ?
May be using ApplyMap and Mapping load
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
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
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.
Another option is to simply do a left join to the main table