Using IsNum() in Load to remove Numeric Values

[Anonymous]

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

[tamilarasu]

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;

[Anonymous]

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,

[bohravanraj · Accepted solution]

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

[Anonymous]

Hi Weston,

just correcting above statement:

LOAD Field1,

     Field2

FROM

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

Thanks,

[#6]

Hi

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;

[ramasaisaksoft]

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);

[Anonymous]

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!

[Anonymous]

Vanraj,

Using the 'NOT' worked perfectly.

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

[tamilarasu]

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?