calculating dimension from multiple tables in script

[Anonymous]

Hi! Thank you for taking the time to read my message and potentially answer my question!

I have four tables that I am importing from Excel (for four different shops).  I would like to add up the columns from each excel sheet, and then sum those four figures to get the total, preferably all in the script.  Please see below what I mean:

Excel sheet 1:          Excel Sheet 2          Excel Sheet 3:               Excel Sheet 4:

Col_count                    Col_count               Col_count                     Col_count

2                                        2                              6                                   2

4                                        10                            3                                   3                  

5                                         3                              1                                   5

so the final bit I would like to put in my script is:

=(sum(excelSheet1_col_cnt) + sum(excelSheet2_col_cnt) + sum(excelSheet3_col_cnt) + sum(excelSheet4_col_cnt))

= ((2+4+5) + (2+10+3) + (6+3+1) + (2+3+5)

= 46

and then I would like to be able to use the variable/dimesion that holds 46 later in the document.

Thanks again for your time and help!

[ahaahaaha]

Hi Natascha,

May be like this (look attached files)

Table1:

Directory;

LOAD

RowNo() as ID,

Col_count,

Rangesum(Col_count, peek( 'Col_count_Sum' ) ) as Col_count_Sum

FROM

[34.xlsx]

(ooxml, embedded labels, table is Sheet1);

Directory;

LOAD

RowNo() as ID,

Col_count,

Rangesum(Col_count, peek( 'Col_count_Sum' ) ) as Col_count_Sum

FROM

[34.xlsx]

(ooxml, embedded labels, table is Sheet2);

Directory;

LOAD

RowNo() as ID,

Col_count,

Rangesum(Col_count, peek( 'Col_count_Sum' ) ) as Col_count_Sum

FROM

[34.xlsx]

(ooxml, embedded labels, table is Sheet3);

Directory;

LOAD

RowNo() as ID,

Col_count,

Rangesum(Col_count, peek( 'Col_count_Sum' ) ) as Col_count_Sum

FROM

[34.xlsx]

(ooxml, embedded labels, table is Sheet4);

varSum = PEEK ( 'Col_count_Sum', -1, 'Table1');

Regards,

Andrey

[mohan_1105]

Hi Natascha,

Use this script,

Temp_Table:

Load

Col_count

From

[34.xlsx]

(ooxml, embedded labels, table is Sheet1);

Concatenate

Load

Col_count

From

[34.xlsx]

(ooxml, embedded labels, table is Sheet2);

Concatenate

Load

Col_count

From

[34.xlsx]

(ooxml, embedded labels, table is Sheet3);

Concatenate

Load

Col_count

From

[34.xlsx]

(ooxml, embedded labels, table is Sheet4);

Table:

Load

Sum(Col_count) as Total

Resident Temp_Table;

Drop table Temp_Table;

//------------Storing the value into the variable...........//

LET vTotal = Total;