Help - Set expression in straight table

[g_santhosh86]

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

[Anonymous]

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)

[Anonymous]

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);

[petter]

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:

[g_santhosh86]

Thanks.

[petter]

Please mark the approritate answer as correct to close this thread