How to differentiate NULL and empty string for AC table?

[Soohoon]

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?

[Jyoti_Yadav]

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

[ParthaSarathy · Accepted solution]

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.

[Soohoon]

Thanks a lot