topic Re: COUNT DISTINCT over 2 seperate fields in QlikView

COUNT DISTINCT over 2 seperate fields

cyberclaus — Sat, 16 Nov 2024 19:16:38 GMT

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

Re: COUNT DISTINCT over 2 seperate fields

Kushal_Chawda — Mon, 20 Jan 2020 10:52:03 GMT

Can you post sample data with expected output?

Re: COUNT DISTINCT over 2 seperate fields

cyberclaus — Mon, 20 Jan 2020 12:44:37 GMT

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

Re: COUNT DISTINCT over 2 seperate fields

Anil_Babu_Samineni — Mon, 20 Jan 2020 12:53:19 GMT

Can't you simply define the fields?

Count(DISTINCT Table1_F1 + Table2_F2)

Re: COUNT DISTINCT over 2 seperate fields

cyberclaus — Mon, 20 Jan 2020 13:58:42 GMT

Hi.

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

Thank you anyway

Best regards

Re: COUNT DISTINCT over 2 seperate fields

Kushal_Chawda — Mon, 20 Jan 2020 14:11:11 GMT

but SW02 installed on 3 assets only..right?

Re: COUNT DISTINCT over 2 seperate fields

Anil_Babu_Samineni — Mon, 20 Jan 2020 14:12:18 GMT

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)

Re: COUNT DISTINCT over 2 seperate fields

cyberclaus — Mon, 20 Jan 2020 14:13:02 GMT

Yes, absolutely correct.

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

Thanks

Greets

Re: COUNT DISTINCT over 2 seperate fields

Kushal_Chawda — Mon, 20 Jan 2020 14:16:36 GMT

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

Re: COUNT DISTINCT over 2 seperate fields

cyberclaus — Mon, 20 Jan 2020 14:21:02 GMT

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

Re: COUNT DISTINCT over 2 seperate fields

Kushal_Chawda — Mon, 20 Jan 2020 14:30:05 GMT

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

Re: COUNT DISTINCT over 2 seperate fields

cyberclaus — Mon, 20 Jan 2020 15:03:54 GMT

Works fine - THANK YOU

You saved my day