Business iQ (Analytics)

cancel
Showing results for 
Search instead for 
Did you mean: 

ADQL showing null rows even when IS NOT NULL is applied

SOLVED
Highlighted
New Member

ADQL showing null rows even when IS NOT NULL is applied

I have an ADQL that is trying to count the number of times a Customer is viewed. I have a data collector to add this data to Transactions.

Here is the query:

 

 

 

 

SELECT segments.userData.CustomerName, segments.userData.CustomerNumber, segments.userData.Agreement, count(segments.userData.CustomerName) FROM transactions WHERE application = "TruckCare-CustomerProfile" AND segments.userData.CustomerName IS NOT NULL

 

 

 

But even with the WHERE clause 'CustomerName IS NOT NULL' There is still a line showing up with all nulls. I know there are Transactions that don't have CustomerName, CustomerNumber, or Agreement on them but I am expecting the 'IS NOT NULL' to filter those out.

 
 

table_data.png

 

Any help much appreciated

 

Thanks

Tags (1)
ADQL showing null rows even when IS NOT NULL is applied
1 REPLY 1
Highlighted
Architect

Re: ADQL showing null rows even when IS NOT NULL is applied

Hi there

 

You need to explicitly set the data types per field for this not to occur:)

 

I just used string for all your fields in your query, however if some are Integers etc., you can update it accordingly

 

SELECT toString(segments.userData.CustomerName), toString(segments.userData.CustomerNumber), toString(segments.userData.Agreement), count(segments.userData.CustomerName) FROM transactions WHERE toString(application) = "TruckCare-CustomerProfile" AND toString(segments.userData.CustomerName) IS NOT NULL



Learn more about me in the Community Member Spotlight Q & A