topic Re: Using IsNum() in Load to remove Numeric Values in QlikView

Using IsNum() in Load to remove Numeric Values

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

Hello QV Friends!

I am trying to use a IsNum() function in a load statement to remove field values that contain ONLY numbers.

The field has values like (4391, 3973, ACLL5, AFMHG) etc. Some are only letters, some only numbers, some both. I want QV to exclude any field values that are made up of only numbers.

My statement looks like this:

Table2Name:

Left Keep (Table1Name)

Load

[Field 1]

[Field 2]

...

From

..\..\..\..\Folder1\Folder2\Folder3\File.Filename.QVD

Where

[Field 1]='US'

IsNum([Code Name]=0)

;

But when I view the listbox for the remaining Codes, is still shows codes that are comprised only of Numbers.

I created a table box comparing the Codes and the result of "=IsNum(Code Name)" so I could verify it was evaluating as I thought it should and it seems to be evaluating correctly (i.e. numbers are getting a "-1" result and alphanumeric are getting a "0").

Any advice on how I can remove these number-only codes from being loaded?

Thanks in advance!

-misterkingsley

Re: Using IsNum() in Load to remove Numeric Values

tamilarasu — Tue, 01 Nov 2016 03:21:37 GMT

Hi Weston,

I think your where clause syntax is not correct. You can try below one.

Table2Name:

Left Keep (Table1Name)

Load

[Field 1]

[Field 2]

...

From

..\..\..\..\Folder1\Folder2\Folder3\File.Filename.QVD

Where

[Field 1]='US' And IsNum([Code Name]) =0;

Re: Using IsNum() in Load to remove Numeric Values

Anonymous — Tue, 01 Nov 2016 04:12:58 GMT

Hi Weston,

Here I am assuming that you want to extract only numeric values, so please use following...

LOAD Field1,

Field2

FROM

(ooxml, embedded labels, table is Sheet1) where IsNum(Field2);

Thanks,

Re: Using IsNum() in Load to remove Numeric Values

bohravanraj — Tue, 01 Nov 2016 04:18:03 GMT

Table2Name:

Left Keep (Table1Name)

Load

[Field 1]

[Field 2]

...

From

..\..\..\..\Folder1\Folder2\Folder3\File.Filename.QVD

Where Match([Field 1],'US') AND

NOT IsNum([Code Name]);

Re: Using IsNum() in Load to remove Numeric Values

Anonymous — Tue, 01 Nov 2016 04:22:53 GMT

Hi Weston,

just correcting above statement:

LOAD Field1,

Field2

FROM

(ooxml, embedded labels, table is Sheet1) where IsNum(Field2) and [Field 1]='US';

Thanks,

Re: Using IsNum() in Load to remove Numeric Values

Tue, 01 Nov 2016 05:08:36 GMT

Try this below

Temp:

LOAD * Inline [

File, Value

123, 10000

abc, 20000

123abc, 30000

234, 40000

bbb, 50000

23dv,45236

];

Test:

LOAD *, 'Test' as Test

Resident Temp Where IsText(File);

DROP Table Temp;

Re: Using IsNum() in Load to remove Numeric Values

ramasaisaksoft — Tue, 01 Nov 2016 05:46:15 GMT

Hi Weston,

check either UMA MAHESH example

else

fallow the below code with the help of Not isnum() function.

Temp:

LOAD * Inline [

File, Value

123, 10000

abc, 20000

123abc, 30000

234, 40000

bbb, 50000

23dv,45236

];

Test:

LOAD *, 'Test' as Test

Resident Temp Where not IsNum(File);

Re: Using IsNum() in Load to remove Numeric Values

Anonymous — Tue, 01 Nov 2016 13:46:34 GMT

Tanil,

Thanks for the response. I forgot to include the "AND" in my post, but I did have it in my script.

Good catch though!

Re: Using IsNum() in Load to remove Numeric Values

Anonymous — Tue, 01 Nov 2016 13:48:14 GMT

Vanraj,

Using the 'NOT' worked perfectly.

Do you know why "NOT IsNUM()" pulls out the numeric fields but IsNUM()=0 not do it?

Re: Using IsNum() in Load to remove Numeric Values

tamilarasu — Tue, 01 Nov 2016 17:39:30 GMT

Hello Weston,

Actually, what I meant to say was you missed to add "And" statement (You said you forgot to include "And", so that's okay ) and Isnum function syntax is not correct.

The below line

IsNum([Code Name]=0)

Should be

IsNum([Code Name]) = 0

Is this not working for you?