topic Re: Filter Values in Load Script in QlikView

Filter Values in Load Script

qlikwiz123 — Sat, 16 Nov 2024 00:18:27 GMT

Hi,

I have a field that has numerical values and combination of Alphabets and Numericals.

Example:

457362

A628408

8357832

B7532805

V283755

How do I load only rows with numbers and not the combination?

Output:

457362

~~A628408~~

8357832

~~B7532805~~

~~V283755~~

Re: Filter Values in Load Script

Kushal_Chawda — Thu, 16 Jul 2020 22:48:55 GMT

Try below

Data:

load *

FROM Source

Where isnum(Field);

Re: Filter Values in Load Script

qlikwiz123 — Fri, 17 Jul 2020 20:29:45 GMT

Is there a way to filter number-character values and only bring Alphabetical Strings?

Thought I could use IsText(Field) but that returns -1 even for number-character values

Re: Filter Values in Load Script

Kushal_Chawda — Fri, 17 Jul 2020 20:44:35 GMT

Try below

Data:

load *

FROM Source

Where not isnum(Field);

Re: Filter Values in Load Script

qlikwiz123 — Fri, 17 Jul 2020 21:00:09 GMT

Hi,

This is not working, It shows both Text and Text-Number Combination values

Re: Filter Values in Load Script

Kushal_Chawda — Fri, 17 Jul 2020 21:43:39 GMT

what was your original requirement ? Was it not to filter just number?

Re: Filter Values in Load Script

Kushal_Chawda — Fri, 17 Jul 2020 22:02:33 GMT

try below

Data: Load * where Flag=1; Load *, if(isnum(if(len(trim(KeepChar(Field,'0123456789')))>0,KeepChar(Field,'0123456789'))) and istext(if(len(trim(KeepChar(Field,'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ')))>0, KeepChar(Field,'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ'))),1,0) as Flag Inline [ Field 123 ABC 12BC ];