Qlik Sense replace second occurrence of dash (-) with semicolon (;)

[piyush_s11]

Hi Team, I have a field where it consist combination of two fields with dash (-), I want to replace the dash (-) which is second occurrence with semicolon (;)

Please find below table example for the same.

program_service_name                                                     expected output
PR-032419-Basel IV                                                               PR-032419; Basel IV
PR-038690-SA-CCR for PRA and FINMA LVE                PR-038690; SA-CCR for PRA and FINMA LVE
PR-038691-Capital Strategic Alignment                       PR-038691; Capital Strategic Alignment
PR-039121-PRA Program                                                    PR-039121; PRA Program
PR-041121-Continuous Compliance                             PR-041121; Continuous Compliance
PR-042921-Capital End of Life                                         PR-042921; Capital End of Life
PR-043707-PRA Basel 3 Reforms                                    PR-043707; PRA Basel 3 Reforms
BC-04529-Continuous Compliance                              BC-04529; Continuous Compliance
BC-04535-B3 Migration                                                      BC-04535; B3 Migration

Please note that we have to replace second occurrence of dash (-) as in project code dash (-) is already present. Also in some places Program service name also consist dash(-). 

Qlik Sense Business 

@avinashelite @tresesco @sunny_talwar @kushalthakral @MayilVahanan @Anil_Babu_Samineni @kaushiknsolanki 

 @Anonymous @Anonymous @swuehl 

[kushalthakral · Accepted solution]

Please try like below

Load
*,
SubField(F1,'-',1)&'-'&SubField(F1,'-',2)&';'&SubField(F1,'-',-1)
;
LOAD * INLINE [
F1
P4-1234-abc
];

Thanks

Kushal

[MayilVahanan · Accepted solution]

Hi 

Try like below

LOAD *,Left(program_service_name, FindOneOf(program_service_name,'-',2)-1)&';'&SubField(program_service_name,'-',-1) as Output INLINE [
program_service_name
PR-032419-Basel IV
PR-038690-SA-CCR for PRA and FINMA LV
PR-038691-Capital Strategic Alignment
PR-039121-PRA Program
PR-041121-Continuous Compliance
PR-042921-Capital End of Life
PR-043707-PRA Basel 3 Reforms
BC-04529-Continuous Compliance
BC-04535-B3 Migration
];

O/p:

[piyush_s11]

Hi @kushalthakral 

thanks for your quick help.

one more thing I forgot to add is I have field value as Not Available-Not Available as well how to convert this with syntax Not Available; Not Available with above suggested formula. 

[piyush_s11]

Hi @MayilVahanan 

thanks for your quick help.

one more thing I forgot to add is I have field value as Not Available-Not Available as well how to convert this with syntax Not Available; Not Available with above suggested formula.