Help me with the script in Qliksense ?

[ashmitp869]

Hi there,

If anyone can suggest me to get my result -

I am using Subfield function to achieve my reqirement though partially achieved -

By using the below line  in the script -

,replace(replace([GP Full Name],' (GP)',''),'(GP)','') as [GP Full Name]
,if(TRIM([GP Full Name]) like 'GP*',[GP Full Name],SubField([GP Full Name], ' ', 1) &' ' &SubField([GP Full Name], ' ', 2) ) as [GPB Full Name]

I achieve this marked with red...which is correct but I need to get the surname after comma which is marked with Blue .

How to I get it ? 

Name, Surname is working fine when the scenario is Name Midddlename , Surname , I am missing the Surname ?

Thanks

[rwunderlich]

How about if you change your SubField delimeter to ',' 

,if(TRIM([GP Full Name]) like 'GP*',[GP Full Name],SubField([GP Full Name], ' ,', 1) &' ' &SubField([GP Full Name], ' ,', 2) ) as [GPB Full Name]

-Rob

[ashmitp869]

Hi Rob,

It didn't work. It gave me same result .

Script used -

,[GP Full Name]
,if(TRIM([GP Full Name]) like 'GP*',[GP Full Name],SubField([GP Full Name], ' ,', 1) &' ' &SubField([GP Full Name], ' ,', 2) ) as [GPB Full Name New]

[ashmitp869]

Prev script - I remove the replace function to double check but its only removing the (GP) from the end

,replace(replace([GP Full Name],' (GP)',''),'(GP)','') as [GP Full Name]

[MayilVahanan]

hi @ashmitp869 

Can you send the sample file and expected output in the excel sheet 

[ashmitp869]

attached the excel file

[tresesco]

May be like this?

Load *,
	 SubField("GP Full Name",',',1)&', '& Left(SubField("GP Full Name",',',2),FindOneOf(Trim(SubField("GP Full Name",',',2))&' ', ' -')) as NewName  
	 Inline [
"GP Full Name"
AAMER, SHAMA
AAMER, SHAMA (GP)
ABBOTSFORD, ABBOTSFORD COVE GENERAL PRACTICE PRACTICE(GP)
ABDALLAH, MOHAMMED KAREEM(GP)
ABDEL SAYED, ODETTE (GP)
ABDEL-WAHAB, MOHSEN (GP)
AL SAMAIL, ALI
AL SHAHABI, ENAS NABHAN(GP)
AL-HORANI, GEORGE (GP)
ALEX-OHUNYON, ONOSEREBAH (GP)
ALFORDS POINT, ALFORDS POINT MEDICAL CENTRE PRACTICE(GP)
ALICE SPRINGS, MALL MEDICAL CENTRE (GP)
ANDRADE HINOJOSA, MARTHA JEANETTE(GP)
ANG, AH-LEN
COFFS HARBOUR, COFFS HARBOUR GP SUPER CLINIC (GP)
COFFS HARBOUR, NORTHSIDE HEALTH (GP)
] (delimiter is '|');

[MayilVahanan]

Hi

Try like below

LOAD
"GP Full Name",
"Expected Output",
SubField("GP Full Name", ',',1)&', ' & If(Len(TextBetween("GP Full Name", ', ', ' '))=0, SubField(Replace("GP Full Name",'-',','), ',',2),TextBetween("GP Full Name", ', ', ' ')) as EO
FROM [lib://DataFiles/sample_dass21.docx.xlsx]
(ooxml, embedded labels, table is SAMPLE);