Sum by creteria

[sculptorlv]

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!

[sculptorlv]

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:

     LOAD

          FA_Number,

          FA_Location_Subcode,

          FA_Class

     RESIDENT DATASOURCE;

TEST2:

     LOAD

          FA_Location_Subcode,

          FA_Clas

     RESIDENT TABLE2;

[jaumecf23]

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:

     LOAD

          FA_Number,

          FA_Location_Subcode,

          FA_Class

     RESIDENT DATASOURCE;

TEST2:

     LOAD

          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:

     LOAD

          FA_Location_Subcode,

          FA_Clas

     RESIDENT DATASOURCE;

Drop Table DATASOURCE;

[sculptorlv]

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