How to differentiate NULL and empty string for AC table?

Question

Hi, I was checking my data on campaign server, using advanced filter on data schema.

And there is a table with fields that some of values are set as Null and others as an empty string, "".

I was trying to differentiate these two types, so tried filtering with condition as below,

[field A] is equal to ""

and

[filed A] is empty

But for both cases, query is generated as "[fieldA] is NULL"

But when I filtered with [filter A] is not empty, I still see the empty values, and when I exported it as a .csv file those values are marked as an empty cells.

Does anyone know a way to resolve this issue? or do any of you know how AC save empty string values?

ParthaSarathy · Accepted Answer

Hi @soohoon ,In query > additional data > add the below expression (lets consider alias as @identifyNull )Coalesce(@fieldA, 'NULL')This will result all null values as a string 'NULL' and empty values still remains empty. And use a split activity after query, and filter now as @identifyNull = 'NULL' to get all null values, and one more subset toidentify empty values and enable complement to get rest valid records with values.

Jyoti_Yadav · Answer

Hi @soohoon ,

The behavior you're observing suggests that the database underlying treats both NULL and empty strings ("") as equivalent when using certain filtering methods within the advanced filter interface.

You can add an additional condition in your query to avoid getting empty fields in your output:

"[field A] is not equal to "" AND [filter A] is not empty"

Thanks,

Jyoti

Sign up

Login with SSO

Login to the community

Login with SSO

Scanning file for viruses.

This file cannot be downloaded