12 Replies Latest reply: Jun 29, 2017 5:05 AM by Ruslans Klimovs

# Sum by creteria

Hello, I have such results from SQL select:

Client nr.     Device Type     Device nr.

Client 1          Type_A          12

Client 1          Type_A          13

Client 1          Type_B          14

Client 2          Type_C          16

I need to create report table, where I can see, how much devices of each type has each client:

Client nr.     Device Type          Quantity

Client 1          Type_A               2

Client 1          Type_B               1

Client 2          Type_C               1

How can I create necessary expression?

• ###### Re: Sum by creteria

Here you have one solution:

test:

Client nr.,     Device Type,     Device nr.

Client 1 ,         Type_A  ,        12

Client 1 ,         Type_A  ,        13

Client 1  ,        Type_B  ,        14

Client 2 ,         Type_C    ,      16

];

test2:

[Client nr.],

[Device Type],

count([Device nr.]) as Quantity

Resident test

Group by [Client nr.],[Device Type];

drop table test;

• ###### Re: Sum by creteria

Thanks for reply, but I was thinking about front end code in Straight table report.

• ###### Re: Sum by creteria

Then create a table like this:

Two dimensions: Client nr. and Device Type

One expression: count([Device nr.])

• ###### Re: Sum by creteria

I do something wrong with a syntax

TEST:

FA_Number,

FA_Location_Subcode,

FA_Class

;

TEST2:

FA_Location_Subcode,

FA_Class,

COUNT(FA_Number) AS Quantity_Total

Resident TEST

Group by

FA_Location_Subcode,

FA_Class;

DROP TABLE TEST;

SQL SELECT

TABLE_FIXED_ASSETS."No_" AS FA_Number,

TABLE_FIXED_ASSETS."FA Location SubCode" AS FA_Location_Subcode,

TABLE_FACLASS.Code AS FA_Class

FROM *********** AS TABLE_FIXED_ASSETS

• ###### Re: Sum by creteria

Forget what I said in my first comment about to create a new table called : test2 using group by. You don't need to do that.

Create a table as I said in the second commentary with the data that you get directly from your SQL Data Base.

• ###### Re: Sum by creteria

Stil can you please specify, how I can use LOAD in my case. I will use it in many other situations.

• ###### Re: Sum by creteria

I don't undertand what you mean. You want to know how create a table using group by in your script?

• ###### Re: Sum by creteria

In this case you have the table test loaded from the SQL data base. Then you want to do some transformation using the data from the table test. Then you use the Resident to point that table test and start the transformation in a new table without modifying the original table.

• ###### Re: Sum by creteria

Thank you, I already have got the idea, but can't get in with the syntax. Why I got error.... Why "Table not found"?

DATASOURCE:

SQL SELECT * from DB;

TABLE2:

FA_Number,

FA_Location_Subcode,

FA_Class

RESIDENT DATASOURCE;

TEST2:

FA_Location_Subcode,

FA_Clas

RESIDENT TABLE2;

• ###### Re: Sum by creteria

I think that the problem is that Datasource table and Table2 table will have exactly the same Information and then Qlikview automatically concatenates the table2 inside datasource table. Then as table2 has been concatenated inside datasource, table2 has dissapeared. Then when it tries to do a resident table2 is failing because this table doesn't exist.

You have two solutions:

Solution 1 (disable the automatic concatenation using the function NoConcatenate):

DATASOURCE:

SQL SELECT * from DB;

NoConcatenate

TABLE2:

FA_Number,

FA_Location_Subcode,

FA_Class

RESIDENT DATASOURCE;

TEST2:

FA_Location_Subcode,

FA_Clas

RESIDENT TABLE2;

Drop Table TABLE2;

Solution 2 (In this case you can directly use the datasource table for the resident. The intermediate table Table2 is not necessary):

DATASOURCE:

SQL SELECT * from DB;

TEST2:

FA_Location_Subcode,

FA_Clas

RESIDENT DATASOURCE;

Drop Table DATASOURCE;

• ###### Re: Sum by creteria

Thank YOU very much! Now I got it! It is really new useful info for me!!