Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello, I have a query I'm hoping someone can help with. I have two tables I want to join in QlikView on a one-to-many basis, but after the join I want to pick a specific one of the many records to create a one-to-one file. Here are the details:
Table A - one record
ID | Value1 |
---|---|
1 | 10 |
2 | 12 |
Table B - many records
ID | Seq | Value2 |
---|---|---|
1 | 1 | 9 |
1 | 2 | 10 |
1 | 3 | 11 |
2 | 1 | 8 |
2 | 2 | 11 |
2 | 3 | 13 |
Then join them together on ID only where value1 is greater than or equal to value 2
ID | Seq | Value | Value2 |
---|---|---|---|
1 | 1 | 10 | 9 |
1 | 2 | 10 | 10 |
2 | 1 | 12 | 8 |
2 | 2 | 12 | 11 |
Then de-duplicate by picking the highest value2 record
ID | Seq | Value | Value2 |
---|---|---|---|
1 | 2 | 10 | 10 |
2 | 2 | 12 | 11 |
Hope that makes sense, does anyone know how to do it?
Many thanks.
Something like this ...
TableA:
Load * inline [
ID, Value1
1, 10
2, 12
];
left join (TableA)
//TableB:
Load * inline [
ID, Seq, Value2
1, 1, 9
1, 2, 10
1, 3, 11
2, 1, 8
2, 2, 11
2, 3, 13
];
TableC:
Noconcatenate Load ID, Value1 as Value, Seq, Value2 resident TableA where Value1 >= Value2;
Drop Table TableA;
inner join (TableC) Load ID, max(Value2) as Value2 Resident TableC Group By ID;
flipside
Hi James,
This should work (not sure if you need the sequence number too but if you do then the solution Dave Riley provided is great)
TableA:
Load * inline [
ID, Value1
1, 10
2, 12
];
LEFT JOIN (TableA)
Load * inline [
ID, Seq, Value2
1, 1, 9
1, 2, 10
1, 3, 11
2, 1, 8
2, 2, 11
2, 3, 13
];
RESULT:
LOAD ID,
Value1 as Value,
max(Value2) as Value2
RESIDENT TableA
where Value1 >= Value2
group by ID, Value1;
DROP TABLE TableA;
If for the 3rd table you wanted it where the max value is equal to or higher than Value1 (opposite way around - I know because I mis-read it the first time) then mapping will work here. Just adding my example below for this too because I find mapping so useful. Just remember because you want the max number then the order by clause (in descending order) is vital. Mapping is great because it saves re-reading tables when joining them and it only ever maps the first record in (hence the reason of the order by clause).
//this answer will get the max(Value2) where Value2 >= Value1
TableA:
Load * inline [
ID, Value1
1, 10
2, 12
];
TMP:
Load * inline [
ID, Seq, Value2
1, 1, 9
1, 2, 10
1, 3, 11
2, 1, 8
2, 2, 11
2, 3, 13
];
MAP_HIGHEST:
MAPPING Load ID,
Value2
RESIDENT TMP
order by ID, Value2 desc;
DROP TABLE TMP;
RESULT:
LOAD ID,
Value1,
ApplyMap('MAP_HIGHEST', ID, Value1) as Value2
RESIDENT TableA;
DROP TABLE TableA;