topic Re: Help - Set expression in straight table in App Development

Help - Set expression in straight table

g_santhosh86 — Sat, 14 Jul 2018 15:52:49 GMT

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

Re: Help - Set expression in straight table

Anonymous — Sat, 14 Jul 2018 23:34:51 GMT

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)

Re: Help - Set expression in straight table

Anonymous — Sun, 15 Jul 2018 02:30:15 GMT

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

Re: Help - Set expression in straight table

petter — Sun, 15 Jul 2018 09:05:08 GMT

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:

Re: Help - Set expression in straight table

g_santhosh86 — Sun, 15 Jul 2018 21:13:23 GMT

Thanks.

Re: Help - Set expression in straight table

petter — Mon, 16 Jul 2018 22:55:50 GMT

Please mark the approritate answer as correct to close this thread