Filter on empty data

[KonstantinaM]

Hello all,

I have a problem where I need to filter on empty data in a specific column. So far, I’ve tried using the NullAsValue FIELD; and Set NullValue = 'NULL'; along with calling the EmptyIsNull(FIELD) in the load statement, but with no success. I’ve also tried the following in the load statement: If(FIELD = '-', Null(), FIELD) AS FIELD, but it still doesn’t work.

Visually, I would like to filter on the - values, which basically are empty, not null.

Here’s a visual for better understanding:

ID    FIELD
0      Value
1       -
2      Value
3      -
4      -
5     Value

Does anyone have a solution for this?

[Clement15]

Hello,

You can try this:

if(len(FIELD)>0,FIELD,null()) as FIELD

[Qrishna]

you can do below:

Data:
LOAD *,
LEN(TRIM(FIELD)) as Len,
IF(LEN(TRIM(FIELD))>1, 0, 1) as Flag;
LOAD * INLINE [
ID, FIELD
0, Value
1, -
2, Value
3, -
4, -
5, Value
];

//you dont need this. this to show full data for comparision
QUALIFY *;
😧
LOAD * RESIDENT Data;

[kevincase]

You should be able to use NullAsValue.

Try:

NULLASVALUE FIELD;
Set NullValue='-';

MyData:

Load

   ID,

   FIELD;

...........

This should set the rows will null to a dash (-).  You should be able to then filter FIELD by selecting -

However, if your data contains Nothing or NoValue, you will need to check the length of the field and set it accordingly.

IF(LEN(TRIM(FIELD))>1, FIELD, '-') as FIELD

Kevin .....

[KonstantinaM]

Thank you very much for your answer. It is not recognizing the - as a character. The -(dash) is created after not finding compatible data on the join. I mean it is being generated as a placeholder when the join operation does not find a matching value. So this does not work for my case.

[KonstantinaM]

Thank you very much for your answer. It is not recognizing the - as a character in order to trim it. The -(dash) is being generated as a placeholder when the join operation does not find a matching value. So this does not work for my case.

[KonstantinaM]

Thank you very much for your answer. As I previously said it is not recognizing the - as a character in order to trim it . The -(dash) is  generated as a placeholder when the join operation does not find a matching value. So this does not work for my case.

[marcus_sommer]

The mentioned NULL and/or EMPTY checks will be working if they are applied against NULL/EMPTY values which means in you case after the join. It means practically to apply a following resident-load to perform the wanted transformation.

Another approach would be not to join the information else to map them because applymap() has a third parameter which could be used to define any default-value.