Qlik Sense find specific value from a string

[piyush_s11]

Hi,

I have attached an excel sheet. In that excel sheet from column A I wanted to separate a specific value which starts  with SUCI-XXXX. if the string doesn't have text as SUCI then it should give me NO_SUCI as a result.

Ex: if coulmn A has string "Accruals for SUCI-3288_Correction" then it should give me the value as SUCI-32888 & if the string doesn't have word SUCI then it should give me NO-SUCI as a result.

@ChristofSchwarz @Anonymous @avinashelite @tresesco @MayilVahanan @Anil_Babu_Samineni @sunny_talwar @Anonymous @prabir_c @MarcoWedel 

Thanks

[prabir_c]

Hi Piyush,

Try wildmatch function like this-

Directory;
LOAD requirement_name,
if(WildMatch(requirement_name,'*SUCI-????*')=1,'SUCI','NO_SUCI') as Req_Type
FROM
[C:\Users\abc\Downloads\1d63dbb0-7ac4-4b21-826b-931692fafc25.xlsx]
(ooxml, embedded labels, table is Sheet1);

[piyush_s11]

Hi @prabir_c 

Thanks for your reply.

The solution you have provided will only give me SUCI & NO-SUCI however I want SUCI along withe number i.e SUCI-XXXX

Eg: if coulmn A has string "Accruals for SUCI-3288_Correction" then it should give me the value as SUCI-32888 & if the string doesn't have word SUCI then it should give me NO-SUCI as a result.

Thanks

[tresesco · Accepted solution]

Try like:

If(WildMatch(requirement_name, '*SUCI*'), Mid(requirement_name,Index(requirement_name, 'SUCI'),9), 'No SUCI') as SUCIFlag

Note: Based on your integer length the the number in bold ('9') might change. If you have five-digit number then use 10 instead. And if the length is variable, you have to write a bit more complex expression.