Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Welcome to Qlik Community! Check out our new navigation! FIND OUT MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
nlamprecht
Contributor III
Contributor III

calculating dimension from multiple tables in script

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!

2 Replies
ahaahaaha
Partner - Master
Partner - Master

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
Partner - Creator III
Partner - Creator III

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;