topic Re: How to detect empty field in QlikView

How to detect empty field

marieclaire — Wed, 25 Nov 2020 16:16:04 GMT

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 !

Re: How to detect empty field

tresB — Tue, 14 Aug 2018 10:18:41 GMT

The same statement worked for me.

Re: How to detect empty field

marcus_sommer — Tue, 14 Aug 2018 10:21:52 GMT

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

Re: How to detect empty field

arvind1494 — Tue, 14 Aug 2018 10:31:52 GMT

Hi Christiane Lapeyre,

I am attaching qvw for your reference .

please download it and see the solution

Re: How to detect empty field

BalaBhaskar_Qlik — Tue, 14 Aug 2018 13:14:46 GMT

May be this:

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

Re: How to detect empty field

marieclaire — Tue, 14 Aug 2018 15:32:25 GMT

Hi Arvind,

I can not see any attached qvw.

Re: How to detect empty field

trdandamudi — Tue, 14 Aug 2018 15:50:58 GMT

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,
];

Re: How to detect empty field

marieclaire — Tue, 14 Aug 2018 16:24:49 GMT

Thank you Marcus, that's it !

Re: How to detect empty field

marieclaire — Tue, 14 Aug 2018 16:28:32 GMT

You are right, it works like this.

I had an issue in the original dataset.

Thank you for your help !

Re: How to detect empty field

marieclaire — Tue, 14 Aug 2018 16:30:56 GMT

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

Re: How to detect empty field

marieclaire — Tue, 14 Aug 2018 16:35:20 GMT

Thank you Tresesco. I finally managed to make it work with a new dataset.