Qlik Community

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:

load x,y,z,

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

SQL SELECT x,y,z

FROM DB9;

T2:

load x,y,z,

     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:

load x,y,z,

     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
MVP

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

Try like:

T1:

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

T2:

load x,y,z,

        'AMOUNT100' as AmountList ,

     sum(x) as Sum

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

load 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;

load 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
MVP

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

Try like:

T1:

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

T2:

load x,y,z,

        'AMOUNT100' as AmountList ,

     sum(x) as Sum

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

load 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;

load 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';

ravic906
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:

Load...AMOUNT100 as AMOUNTLIST,

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

SQL SELECT * FROM DB;

Load...AMOUNT200 as AMOUNTLIST,

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

SQL SELECT * FROM DB;

Load...AMOUNT300 as AMOUNTLIST,

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

SQL SELECT * FROM DB;

?

MVP
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