topic calculating dimension from multiple tables in script in QlikView

calculating dimension from multiple tables in script

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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!

Re: calculating dimension from multiple tables in script

ahaahaaha — Wed, 26 Apr 2017 06:36:58 GMT

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

Re: calculating dimension from multiple tables in script

mohan_1105 — Sat, 20 Jan 2018 14:51:03 GMT

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;