topic Re: Filter Values in App Development

Filter Values

agarwal16 — Thu, 15 Sep 2022 06:54:57 GMT

Hi Qlik Experts,

I have a query please help in the solution:

I have a field named as code .

i want to transform the code based on following conditions:

Code should be 5 characters long – can be numeric or alphanumeric
If numeric and <=99999, it is a valid
If alphanumeric, alphabet followed by 4 digits or 4 digits with last character as alphabet .

please help in writing the conditions.

Re: Filter Values

brunobertels — Thu, 15 Sep 2022 08:50:32 GMT

May be this

If(

Len(

KeepChar(MyField,'0123456789')

)=5,'Valid', 'No Valid'

)

Re: Filter Values

agarwal16 — Thu, 15 Sep 2022 09:06:32 GMT

Code are like: A11124, XDFG43, VOILA,1222T,45678,435PA,AC23OT

So output should be : A1124,1222T,45678

Re: Filter Values

RsQK — Thu, 15 Sep 2022 09:52:53 GMT

Hi, this might be it:

//Result mapping table: temp_ml_valid_codes: LOAD 0 AS TEMP AUTOGENERATE 0; //Num.: CONCATENATE (temp_ml_valid_codes) LOAD ROWNO()+9999 AS code AUTOGENERATE 90000; //Text: temp_letters: LOAD SUBFIELD(letter,';') as letter INLINE [ letter A;B;C;D;E;F;G;H;I;J;K;L;M;N;O;P;Q;R;S;T;U;V;W;X;Y;Z ]; JOIN (temp_letters) LOAD ROWNO()+ 999 AS int AUTOGENERATE 9000; CONCATENATE (temp_ml_valid_codes) LOAD letter & int AS code RESIDENT temp_letters; CONCATENATE (temp_ml_valid_codes) LOAD int & letter AS code RESIDENT temp_letters; DROP TABLE temp_letters; ml_valid_codes: MAPPING LOAD code, code RESIDENT temp_ml_valid_codes; DROP TABLE temp_ml_valid_codes; temp_codes: LOAD *, APPLYMAP('ml_valid_codes',code,'NOT VALID') AS valid_code INLINE [ code A1124 XDFG43 VOILA 1222T 45678 435PA AC23OT 1 12 123 1234 12345 A1 A12 A123 A1234 A12345 1A 12A 123A 1234A 12345A ];

Re: Filter Values

MarcoWedel — Mon, 19 Sep 2022 21:31:31 GMT

Maybe like this?

table1: LOAD * Inline [ Code A1124 XDFG43 VOILA 1222T 45678 435PA AC23OT ] Where Len(Code)=5 and IsNum(Mid(Code,2,3)) and (IsNum(Left(Code,1)) or IsNum(Right(Code,1)));

Re: Filter Values

SunilChauhan — Tue, 20 Sep 2022 04:51:02 GMT

Load *,
If(Isnum(Mid(Code,2,5)) or Isnum(Mid(Code,1,4)),Code) as Test inline [
Code
A11124
XDFG43
VOILA
1222T
45678
435PA
AC23OT
];

see the attached

Re: Filter Values

MarcoWedel — Tue, 20 Sep 2022 06:07:42 GMT

or shorter

table1: LOAD * Inline [ Code A1124 B12345 XDFG43 VOILA 1222T 45678 435PA AC23OT ] Where Len(Code)=5 and (IsNum(Left(Code,4)) or IsNum(Right(Code,4)));