Calculate field and Joining tables

[MindaugasBacius]

Hei,

What is the best practice to calculate field F4 and join tables?

A:
LOAD * INLINE [
    F1, F2
    1, 2
    3, 4
];


Left join (A)
LOAD * INLINE [
    F1, F3
    1, 2
    2, 3
    3, 4
];


Left Join (A)
Load
  F1,
  F1 * F2 / F3 as F4
Resident A;

On a small scope it works fine but when I adapt the same logic to large data set - it stuck.

Any ideas how to improve the script by calculating F4 without changing lines 1-15 would be much appreciated.

Thank you!

[oknotsen · Accepted solution]

I would advise turning one of the two tables into a Mapping table and use the ApplyMap() function instead of joining. The advantage is that with an applymap you can immediately do your calculation instead of having to do more lines of code with joining residents.

[sunny_talwar]

May be use ApplyMap() on a resident table instead of join.

[oknotsen]

I assume that in reality you do not have all in InLine tables, so assume in this code that you do not have Atmp and are not doing a resident load on Atmp but instead are loading from your source:

B:
Mapping
LOAD * INLINE [
F1, F3
1, 2
2, 3
3, 4
];

Atmp:
LOAD * INLINE [
F1, F2

1, 2,
3, 4,
];

A:
LOAD
F1,
F2,
ApplyMap('B', F1, 'unknown') as F3,
F1 * F2 / ApplyMap('B', F1, 'unknown') as F4
Resident Atmp
;

[Anonymous]

I think you can do like this as well?

A:

LOAD * Inline

[F1, F2

1,2

3,4

];

left join(A)

B:

LOAD * Inline

[F1,F3

1,2

2,3

3,4

];

NoConcatenate

C:

LOAD *,

(F1*F2)/F3 as F4

Resident A;

drop Table A;

[jyothish8807]

Hi Bacius,

Try like this:

A: 

LOAD * INLINE [ 

F1, F2 

1, 2 

3, 4 

]; 

Left join (A) 

LOAD * INLINE [ 

F1, F3 

1, 2 

2, 3 

3, 4 

]; 

noconcatenare

TempA:

load *,

F1 * F2 / F3 as F4

resident A;

Drop Table A; //if required

Else you can use apply map as other suggested. Left join always effect the performance of the application

Regards

KC

[oknotsen]

Since no tables will have the same structure at any point, there is no need for NoConcatenate.

Plus it still is too much work / code compared to the mapping solution .

[Anonymous]

I have not done any extraa work

I just replaced Join statement with NoConcatenate and then dropped main table after resident

[oknotsen]

Not extra, but you can do it with less work .