Using AND & OR conditions in set analysis to get the total count of unique values across two fields

[akelleway]

Hi,

I have two fields; OriginCountryName and DestinationCountryName - in the data, these are used to show the origin and destination of a long list of jobs.

There are 159 unique origin countries and 184 unique destination countries, with 191 unique values across the two which I calculated using the below expression:

count(distinct OriginCountryName) + (count({$<DestinationCountryName-=p(OriginCountryName)>} distinct DestinationCountryName))

However, what I am trying to do, is the same thing, but only for jobs where at least one of two fields called CreditorAccountFF and DebtorAccountFF is not null. I believe this would be done in set analysis like this: {<CreditorAccountFF -= {"''"}> + <DebtorAccountFF -= {"''"}>} - but I have just been unable to get it all working in one expression.

Unfortunately I am unable to share any data due to the sensitivity of it, but any suggestions/help would be much appreciated.

[Chanty4u]

Try this 

Count(DISTINCT {<  

    CreditorAccountFF = {"*"}> + <DebtorAccountFF = {"*"}>  

>} OriginCountryName)  

+  

Count(DISTINCT {<  

    CreditorAccountFF = {"*"}> + <DebtorAccountFF = {"*"},  

    DestinationCountryName -= p(OriginCountryName)  

>} DestinationCountryName)

[akelleway]

Hi Chanty,

Thanks for your response, however it is not working unfortunately.

Having counted manually, the first part returns the correct result, as there are 107 Origin Countries and this number is returned if I split the expression in two.

The second half seems to be where the issue is, as there 39 additional Destination Countries which should be returned here, but the result it is giving is 131. Making a total of 238 rather than the expected 146.

[marcus_sommer]

NULL means there is NOTHING what has been stored - it couldn't be selected or accessed in any way - at least not directly (indirect ways are complex and have a lot of dependencies and disadvantages).

If there is NULL directly in the load it could be queried/replaced with approaches like isnull(), len(trim()), coalesce() ... or with NULL variables.

But it's not within a table else the NULL is the result of an association between tables - missing key-values on any side - it couldn't be grabbed in any way. The resolution here are adjustments to the data-model, for example by checking the missing keys against each other and populating them appropriate.