Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hey ALL I need to know how I can mapp table 1 and table 2 in the script to get the table 3: HOW YOU CAN HELP THANK YOU
Table 1: | |||
Code | Bucket | ||
MR | B2 | ||
MR | B3 | ||
HC | B4 | ||
HC | B5 | ||
HC | B6 | ||
HC | B7 | ||
HC | B8 | ||
HC | B9 | ||
WO | B10 | ||
WO | B12 | ||
WO | B13 | ||
Table2 : | |||
Unit CODE | Unit_Desc | Bucket-Code | |
COLL1 | A | MR | |
COLL2 | S | MR | |
COLL3 | D | HC | |
COLL4 | F | HC | |
COLL5 | G | HC | |
COLL6 | H | WO | |
COLL7 | J | WO | |
COLL11 | K | MR | |
COLL12 | L | MR | |
COLL15 | Z | HC | |
COLL16 | X | HC | |
COLL17 | C | MR | |
COLL18 | V | MR | |
COLL19 | N | WO | |
COLL20 | M | WO | |
COLL21 | B | HC | |
COLL22 | Q | MR | |
COLL24 | W | MR | |
COLL25 | E | MR | |
COLL26 | R | MR | |
COLL27 | T | HC | |
Table3 Ouput: | |||
Unit Code | Unit_Desc | Bucket-Code | Bucket |
COLL1 | A | MR | B2 |
COLL1 | A | MR | B3 |
COLL2 | S | MR | B2 |
COLL2 | S | MR | B3 |
COLL11 | K | MR | B2 |
COLL11 | K | MR | B3 |
COLL12 | L | MR | B2 |
COLL12 | L | MR | B3 |
COLL17 | C | MR | B2 |
COLL17 | C | MR | B3 |
COLL18 | V | MR | B2 |
COLL18 | V | MR | B3 |
COLL22 | Q | MR | B2 |
COLL24 | W | MR | B2 |
COLL24 | W | MR | B3 |
COLL25 | E | MR | B2 |
COLL26 | R | MR | B3 |
COLL3 | D | HC | B4 |
COLL3 | D | HC | B5 |
COLL3 | D | HC | B6 |
COLL3 | D | HC | B7 |
COLL3 | D | HC | B8 |
COLL3 | D | HC | B9 |
COLL5 | G | HC | B4 |
COLL5 | G | HC | B5 |
COLL5 | G | HC | B6 |
COLL5 | G | HC | B7 |
COLL5 | G | HC | B8 |
COLL5 | G | HC | B9 |
COLL15 | Z | HC | B4 |
COLL15 | Z | HC | B5 |
COLL15 | Z | HC | B6 |
COLL15 | Z | HC | B7 |
COLL15 | Z | HC | B8 |
COLL15 | Z | HC | B9 |
COLL16 | X | HC | B4 |
COLL16 | X | HC | B5 |
COLL16 | X | HC | B6 |
COLL16 | X | HC | B7 |
COLL16 | X | HC | B8 |
COLL16 | X | HC | B9 |
COLL21 | B | HC | B4 |
COLL21 | B | HC | B5 |
COLL21 | B | HC | B6 |
COLL21 | B | HC | B7 |
COLL21 | B | HC | B8 |
COLL21 | B | HC | B9 |
COLL27 | T | HC | B4 |
COLL27 | T | HC | B5 |
COLL27 | T | HC | B6 |
COLL27 | T | HC | B7 |
COLL27 | T | HC | B8 |
COLL27 | T | HC | B9 |
COLL6 | H | WO | B10 |
COLL6 | H | WO | B12 |
COLL6 | H | WO | B13 |
COLL7 | J | WO | B10 |
COLL7 | J | WO | B12 |
COLL7 | J | WO | B13 |
COLL19 | N | WO | B10 |
COLL19 | N | WO | B12 |
COLL19 | N | WO | B13 |
COLL20 | M | WO | B10 |
COLL20 | M | WO | B12 |
COLL20 | M | WO | B13 |
:
LEFT JOIN (Table2)
LOAD Code As Bucket-Code, Bucket RESIDENT Table1;
RENAME Table Table2 TO Table3;
:
will do, I guess.
Try this way with mapping load and be insure you have unique values in mapping table.
MapTable:
Mapping
LOAD
Code as Bucket_Code,
Bucket
from Source;
Table2:
Load
[Unit CODE],
Unit_Desc,
Bucket-Code as Bucket_Code,
ApplyMap('MapTable',Bucket-Code,'Not Matched')
from source;
Put Bucket-Code between square brackets, or QlikView will turn it into a subtraction. Like
LOAD Code AS [Bucket-Code], ...
Usefull reading: Don't join - use Applymap instead
Hi Mario,
Your output table seems to be missing some lines for example:
Unit CODE | Unit_Desc | Bucket-Code | Bucket |
---|---|---|---|
COLL4 | F | HC | B4 |
COLL4 | F | HC | B5 |
COLL4 | F | HC | B6 |
COLL4 | F | HC | B7 |
COLL4 | F | HC | B8 |
COLL4 | F | HC | B9 |
Maybe this:
Output:
LOAD * INLINE [
Unit CODE, Unit_Desc, Bucket-Code
COLL1, A, MR
COLL2, S, MR
COLL3, D, HC
COLL4, F, HC
COLL5, G, HC
COLL6, H, WO
COLL7, J, WO
COLL11, K, MR
COLL12, L, MR
COLL15, Z, HC
COLL16, X, HC
COLL17, C, MR
COLL18, V, MR
COLL19, N, WO
COLL20, M, WO
COLL21, B, HC
COLL22, Q, MR
COLL24, W, MR
COLL25, E, MR
COLL26, R, MR
COLL27, T, HC
];
Inner Join(Output)
LOAD
Code as [Bucket-Code],
Bucket;
LOAD * INLINE [
Code, Bucket
MR, B2
MR, B3
HC, B4
HC, B5
HC, B6
HC, B7
HC, B8
HC, B9
WO, B10
WO, B12
WO, B13
];