Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have a requirement to create the result as below from table 1 and table 2
Table 1 | |
Veh | Cost |
A | 20000 |
B | 25000 |
C | 26000 |
D | 27000 |
Table 2 | |
Veh | ComparetoVeh |
A | A |
B | A |
C | B |
D | C |
Result | ||||
Veh | ComparetoVeh | Cost of Veh | cost of ComparetoVeh | Delta |
A | A | 20000 | 20000 | 0 |
B | A | 25000 | 20000 | 5000 |
C | B | 26000 | 25000 | 1000 |
D | C | 27000 | 26000 | 1000 |
Have you tried:
Step 1 - load table 1
Step 2 - make a mapping load with resident table 1 (Veh, Cost)
Step 3 - left join table 2 to table 1, (comparetoVeh), ApplyMap(step2), Cost-ApplyMap(step2)
Table1:
LOAD * INLINE [
Veh,Cost
A,20000
B,25000
C,26000
D,27000
];
Let rowcount=NoOfrows('Table1');
Trace (1) Table 1 size: $(rowcount);
MappingCost:
Mapping Load
Veh,Cost
Resident Table1;
Let rowcount=NoOfrows('Table1');
Trace (2) Table 1 size: $(rowcount);
Left Join (Table1)
LOAD * INLINE [
Veh,ComparetoVeh
A,A
B,A
C,B
D,C
];
Let rowcount=NoOfrows('Table1');
Trace (3) Table 1 size: $(rowcount);
Left Join (Table1)
Load
Veh,
ApplyMap('MappingCost', ComparetoVeh, 0) as [cost of ComparetoVeh],
Cost - ApplyMap('MappingCost', ComparetoVeh, 0) as Delta
Resident Table1;
Let rowcount=NoOfrows('Table1');
Trace (4) Table 1 size: $(rowcount);
You could achieve this by unpivoting the second table by using crosstable prefix on the load statement - then it is simple to use set expressions to do the calculations in a (straight) table:
Thanks.
Please mark the approritate answer as correct to close this thread