Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Extract three letters

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. 🙂

 

Labels (2)
2 Solutions

Accepted Solutions
hector
Specialist
Specialist

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

View solution in original post

Taoufiq_Zarra

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;

 

Capture.JPG

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉

View solution in original post

3 Replies
hector
Specialist
Specialist

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

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;

 

Capture.JPG

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
Anonymous
Not applicable
Author

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