Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi, having a problem I'm trying to solve for some days I now try asking you.
I have two tables A and B coming from a sql view. "A" shows all assets where software "A" is installed. "B" shows all assets where software "B" is installed. On some assets software "A" is installed as well as software "B".
Now I want a (distinct) number of assets where "A" or "B" is installed - one or both.
Count(Aggr(DISTINCT Count(DISTINCT A_AssetName), B_AssetName)) doesn't work. I mean it is correct syntactically but the result is wrong.
Can anybody out there pleae help me.
Thank you in advance
Best regards
cyberclaus
It will work in your scenario as well. You can directly use the query to append the data or you can create QVDs using individual SQL queries and then use QVDs to perform any other operation like join.
for SQL, you can use below
Table1:
SQL Select Asset,Software
FROM dbname.table name ;
Concatenate(Table1)
Table2:
SQL Select Asset,Software
FROM dbname.table name ;
Note: Make sure that column names are same in both query for concatenate load
Can you post sample data with expected output?
hope this is readable:
Table 1:
Client01 SW01
Client02 SW01
Server01 SW01
Server02 SW01
Server03 SW01
Server04 SW01
Tabl2 2:
Client02 SW02
Server01 SW02
Server03 SW02
Expected: SW01 / SW02 installed on 6 assets
LIKE (Count(Distinct Table1 + Table2)
Thanks
Best regards
cyberclaus
Can't you simply define the fields?
Count(DISTINCT Table1_F1 + Table2_F2)
Hi.
Sorry no: Count(DISTINCT Table1_F1 + Table2_F2) give 0 (zero).
Thank you anyway
Best regards
but SW02 installed on 3 assets only..right?
My intension like this
[Table 1]:
Load * Inline [
ServerName, Table1_F1
Client01 SW01
Client02 SW01
Server01 SW01
Server02 SW01
Server03 SW01
Server04 SW01
];
[Table 2]:
Load * Inline [
ServerName, Table2_F2
Client02 SW02
Server01 SW02
Server03 SW02
];
Create Table like
Dimension : ServerName
Expression : Count(DISTINCT Table1_F1 + Table2_F2)
Yes, absolutely correct.
In total 6 assets are affected. That's what we need.
Thanks
Greets
[Table 1]:
Load * Inline [
Asset, Software
Client01 SW01
Client02 SW01
Server01 SW01
Server02 SW01
Server03 SW01
Server04 SW01
];
concatenate([Table 1])
[Table 2]:
Load * Inline [
Asset, Software
Client02 SW02
Server01 SW02
Server03 SW02
];
Now you can simple use below expression
=count(distinct Asset)
Thanks to you both.
As far as I understand I would need to load the data with a LOAD instruction. Does this also work with our scenario? We do not load data from files but we use sql statements (SQL SELECT xxx FROM) and get the data via ODBC connection from a remote database. It's not that easy to get the underlying view changed.
Best regards
cyberclaus