Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
sculptorlv
Creator III
Creator III

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?

Thanks in advance!

1 Solution

Accepted Solutions
jaumecf23
Creator III
Creator III

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.

I attach a .qvw that will help you.

View solution in original post

12 Replies
jaumecf23
Creator III
Creator III

Here you have one solution:

test:

LOAD * INLINE [

    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:

load

[Client nr.],

[Device Type],

count([Device nr.]) as Quantity

Resident test

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

drop table test;

sculptorlv
Creator III
Creator III
Author

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

jaumecf23
Creator III
Creator III

Then create a table like this:

Two dimensions: Client nr. and Device Type

One expression: count([Device nr.])


Captura.PNG

sculptorlv
Creator III
Creator III
Author

I do something wrong with a syntax

TEST:

LOAD

FA_Number,

FA_Location_Subcode,

FA_Class

;

TEST2:

LOAD

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

error.jpg

jaumecf23
Creator III
Creator III

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.

I attach a .qvw that will help you.

sculptorlv
Creator III
Creator III
Author

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

jaumecf23
Creator III
Creator III

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

sculptorlv
Creator III
Creator III
Author

I want to get idea about LOAD from Resident Load.

jaumecf23
Creator III
Creator III

Basically the Resident Load is used to load a table that you have already loaded in the script in a new table.

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.

Here you have a link that provides more information : QlikView Resident Load