Extract three letters

[Anonymous]

Hello qlikers :),

is there a option like regex in Qlik Sense?

For example:

Test
AAA/GH
ABD/JKO, BBB/KLA
CCC/IKO, HHH/JSJ
undefined, HHH/IOP
AA/ABC

I only want the bold letters. I tried it with

Left([Test],3) as newTest,

But the problem is this gets me only the 3 letters, but I want also ABD/JKO, BBB/KLA the second third letters. And something like undefined should be skip. Do you know something like regex in Qlik Sense?

Thank you in advance! Looking forward to reading some answers. 🙂

[hector · Accepted solution]

Hi

Did some test and it works, check it out

LOAD
rowno() as ID,
text,
subfield(trim(subfield(text,',',rangesum(1,index(text,',')/index(text,',')))),'/',1) as [3letters]
INLINE [
    text
    AAA/GH
    "ABD/JKO, BBB/KLA"
    "CCC/IKO, HHH/JSJ"
    "undefined, HHH/IOP"
    AA/ABC
];

I have to put the double quotes to have the 2 values as 1 column

regards

[Taoufiq_Zarra · Accepted solution]

Data:
load 
trim(subfield(Test,',')) as tmp,* inline [
Test
AAA/GH;
ABD/JKO, BBB/KLA;
CCC/IKO, HHH/JSJ;
undefined, HHH/IOP;
AA/ABC;
](delimiter is ';');

output:
noconcatenate

load 
if(index(tmp,'/')>0,left(tmp,index(tmp,'/')-1))   as newTest,
*
 resident Data;
 
 drop table Data;
 drop field tmp;

[Anonymous]

Ohhh! Thank you very much for your work! 🙂 I'm very happy about that. Thank you!!!