How to detect empty field

[marieclaire]

Hello everybody,

I need to detect empty fields in the 2nd column.

I tried to use

if(len(trim([Id Tool]))=0,'undefined',[Id Tool]) as Id_Tool2

But it does not work.

Anybody can help ?

Thank you !

[tresesco]

The same statement worked for me.

[marcus_sommer · Accepted solution]

Maybe there are any other invisible chars within the values. Of course they could be identified and removed but it might enough to tweak your condition (at least by looking on your screenshot) a bit to get the wanted results. I mean:

if(len(trim([Id Tool])) < 2,'undefined',[Id Tool]) as Id_Tool2

- Marcus

[arvind1494]

Hi Christiane Lapeyre,

I am attaching qvw for your reference .

please download it and see the solution

[balabhaskarqlik]

May be this:

if(IsNull([Id Tool]),'Undefined',[Id Tool]) as Id_Tool2

[marieclaire]

Hi Arvind,

I can not see any attached qvw.

[trdandamudi]

I do not see any issue and it should work. Copy and paste the below code and run the script:

Data:
Load *,
If(Len(Trim([Id Tool]))=0,'Undefined',[Id Tool]) as Id_Tool2;
Load * Inline [
Date, Id Tool
01/08/2018 17:13:59, COMPANY@148142
01/08/2018 17:12:52, COMPANY@148142
01/08/2018 17:11:50, COMPANY@148142
01/08/2018 15:46:43, COMPANY@150145
01/08/2018 15:46:14, COMPANY@150145
01/08/2018 15:45:25, COMPANY@45577
01/08/2018 15:03:37, COMPANY@56785
01/08/2018 15:02:23, COMPANY@56785
01/08/2018 12:16:42,
01/08/2018 12:15:30, COMPANY@113446
01/08/2018 12:06:49, COMPANY@43075
01/08/2018 12:02:05, COMPANY@86539
01/08/2018 11:32:26, COMPANY@150082
01/08/2018 11:19:38, COMPANY@48992
01/08/2018 10:57:36, COMPANY@150084
01/08/2018 10:06:28, COMPANY@84659
01/08/2018 09:35:19, COMPANY@94343
01/08/2018 09:15:42, COMPANY@150121
01/08/2018 08:48:17,
01/08/2018 08:43:03, COMPANY@149357
01/08/2018 08:27:08, COMPANY@150115
01/08/2018 03:59:48, COMPANY@150112
31/07/2018 22:15:34,
31/07/2018 20:54:59,
];

[marieclaire]

Thank you Marcus, that's it !

[marieclaire]

You are right, it works like this.

I had an issue in the original dataset.

Thank you for your help !

[marieclaire]

Thank you but it does not work with "isNull".