Refering multiple columns in table script using resident load

[onkarkulkarni]

Hi all,

I am trying to load data from xlsx file in order to represent some statistical calculations in dashboard. But I am getting an issue with resident load in script.

Table1
Load
a, b, c
from xyz;    // I have loaded all the basic columns

Table2:
Load
sum(a) as P
resident Table1;   // deriving basic calculations

Table 3:
Load
(c + P) as M
resident Table2;  // deriving advance calculations

I am getting  error as column is c not available.

Please help me correct the script. Thanks in advance.

[snehamahaveer]

Hi Onkar,

You would have to try like below.

Table1
Load
a, b, c
from xyz;    // I have loaded all the basic columns

Table2:
Load
sum(a) as P,

b,

c
resident Table1

Group by b,c;   // deriving basic calculations (add b,c if you want c or b to be used in other tables using resident table 2)

Table 3:
Load
(c + P) as M
resident Table2;  // deriving advance calculations

Give this a try and let me know if it helps.

or a simple one to try would be

Table1
Load
a,

b,

c,

Sum(a) as P,

(c+sum(a)) as M
from xyz

Group by b,c;   (if you want a,b,c to be included then add them if not just the summation calculations should be sufficient)

[onkarkulkarni]

Thanks Sneha for quick reply.

I have 30 basic columns to load and more than 50 statistical calculations so it would be very complex to go with approach 2.

For 1st approach considering that  I want to use just P column, this issue still persists.

Regards,

Onkar Kulkarni

[snehamahaveer]

Hi Onkar,

The logic behind this is - if you are doing a resident load and the column is not in the resident table it would give an error, which was why I added C on table 2 and then used it in table 3.

I can help you more if you are able to provide a sample of data.

Regards,

S