Filter Values

[agarwal16]

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:

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

please help in writing the conditions.

[brunobertels]

Hi 

May be this 

If(

Len(

KeepChar(MyField,'0123456789')

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

) 

[agarwal16]

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

So output should be : A1124,1222T,45678

[RsQK]

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

[MarcoWedel]

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

[SunilChauhan]

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

[MarcoWedel]

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