Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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.
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;
Thanks for reply, but I was thinking about front end code in Straight table report.
Then create a table like this:
Two dimensions: Client nr. and Device Type
One expression: count([Device nr.])
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
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.
Stil can you please specify, how I can use LOAD in my case. I will use it in many other situations.
I don't undertand what you mean. You want to know how create a table using group by in your script?
I want to get idea about LOAD from Resident Load.
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