Solved: Re: COUNT DISTINCT over 2 seperate fields - Qlik Community

cyberclaus · ‎2020-01-20

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

Kushal_Chawda · ‎2020-01-20

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

View solution in original post

Kushal_Chawda · ‎2020-01-20

Can you post sample data with expected output?

cyberclaus · ‎2020-01-20

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

Anil_Babu_Samineni · ‎2020-01-20

Can't you simply define the fields?

Count(DISTINCT Table1_F1 + Table2_F2)

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful

cyberclaus · ‎2020-01-20

Hi.

Sorry no: Count(DISTINCT Table1_F1 + Table2_F2) give 0 (zero).

Thank you anyway

Best regards

Kushal_Chawda · ‎2020-01-20

but SW02 installed on 3 assets only..right?

Anil_Babu_Samineni · ‎2020-01-20

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)

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful

cyberclaus · ‎2020-01-20

Yes, absolutely correct.

In total 6 assets are affected. That's what we need.

Thanks

Greets

Kushal_Chawda · ‎2020-01-20

[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)

cyberclaus · ‎2020-01-20

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

COUNT DISTINCT over 2 seperate fields

2 tables

Count

Distinct