# QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

#### Breathe easy -- you now have more time to plan your next steps with Qlik! QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information. Not applicable

## list serveral sum() of several tables in 1 columm

Hi guys

I have several sum()s like

T1:

sum(x) as AMOUNT100 where (z<=100and y<=100) group by x,y,z;

SQL SELECT x,y,z

FROM DB9;

T2:

sum(x) as AMOUNT200where( (z<=200 and z>100) and (y<=123)) group by x,y,z;

SQL SELECT x,y,z

FROM DB9;

T3:

sum(x)  as AMOUNT300 where( (z<=300 and z>200) and (y<=123)) group by x,y,z;

SQL SELECT x,y,z

FROM DB9;

My questions are:

1. Is it possible to take all sum() in one table?

2. How can I list all sum() in 1 collumm to have a list like : -> for showing on dasboard ..

AMOUNTLIST, sum

AMOUNT100, 10

AMOUNT200, 20

AMOUNT300, 30

Tags (1)
1 Solution

Accepted Solutions MVP

## Re: list serveral sum() of several tables in 1 columm

Try like:

T1:

load * SQL SELECT x,y,z FROM DB9;

T2:

'AMOUNT100' as AmountList ,

sum(x) as Sum

Resident T1 where (z<=100and y<=100) group by x,y,z;

'AMOUNT200' as AmountList,

sum(x) as Sum

Resident T1 where( (z<=200 and z>100) and (y<=123)) group by x,y,z;

'AMOUNT300' as AmountList,

sum(x)  as Sum

Resident T1 where( (z<=300 and z>200) and (y<=123)) group by x,y,z;

Drop Table T1:

Update: where clause could not be used in preceding load, corrcetd accordingly.

The tables would get automatically concatenated to generate one table named 'T2';

5 Replies MVP

## Re: list serveral sum() of several tables in 1 columm

Try like:

T1:

load * SQL SELECT x,y,z FROM DB9;

T2:

'AMOUNT100' as AmountList ,

sum(x) as Sum

Resident T1 where (z<=100and y<=100) group by x,y,z;

'AMOUNT200' as AmountList,

sum(x) as Sum

Resident T1 where( (z<=200 and z>100) and (y<=123)) group by x,y,z;

'AMOUNT300' as AmountList,

sum(x)  as Sum

Resident T1 where( (z<=300 and z>200) and (y<=123)) group by x,y,z;

Drop Table T1:

Update: where clause could not be used in preceding load, corrcetd accordingly.

The tables would get automatically concatenated to generate one table named 'T2';

Contributor III

## Re: list serveral sum() of several tables in 1 columm

Hi,

Use a different letter or a number as a unique field in every table..

Like load 'i' as k,x,y,z,sum(x) as AMOUNT200where( (z<=200 and z>100) and (y<=123)) group by x,y,z;

Now,concatenate all the tables and u will find the sum(whatever) as a single column

Here the letter 'i' will be used to identify which table the sum belongs to.

HTH

Ravi N. Not applicable

## Re: list serveral sum() of several tables in 1 columm

Dont you mean to rename the sums?

like

T1:

sum() as AMOUNT100 where.. group by ..;

SQL SELECT * FROM DB;

sum() as AMOUNT200 where.. group by ..;

SQL SELECT * FROM DB;

sum() as AMOUNT300 where.. group by ..;

SQL SELECT * FROM DB;

? MVP

## Re: list serveral sum() of several tables in 1 columm

As you wanted 'Sum' to be a new column name, my above code would work. Also note my updated script (a corrected one - you can't use where clause in preceding load) Not applicable

## Re: list serveral sum() of several tables in 1 columm

thank you very much Community Browser