Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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
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
];
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
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
];