Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
LINKEDIN LIVE: Democratizing data to enhance customer-centricity. JULY 29TH REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
cyberclaus
Contributor II
Contributor II

COUNT DISTINCT over 2 seperate fields

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

Labels (3)
1 Solution

Accepted Solutions
Kushal_Chawda

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

11 Replies
Kushal_Chawda

Can you post sample data with expected output?

cyberclaus
Contributor II
Contributor II
Author

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

Can't you simply define the fields?

Count(DISTINCT Table1_F1 + Table2_F2)

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
cyberclaus
Contributor II
Contributor II
Author

Hi.

 

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

Thank you anyway

Best regards

 

Kushal_Chawda

but SW02 installed on 3 assets only..right?

Anil_Babu_Samineni

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)

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
cyberclaus
Contributor II
Contributor II
Author

Yes, absolutely correct.

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

 

Thanks

Greets

 

 

Kushal_Chawda

[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
Contributor II
Contributor II
Author

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