Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
pgbehles1
New Contributor

is there a function that can act like "find" in excel that i can substitute into qlikview?

I am trying to format a field of names from LAST, FIRST MIDDLE to just FIRST LAST. in Excel I have this formula written out and am trying to mimic it, if possible (or find an alternative solution) for qlikview.

formula 1: =IF(E2="","",RIGHT(E2,LEN(E2)-FIND(",",E2)))&" "&IF(E2="","",LEFT(E2,FIND(",",E2)-1)) <-- this turns LAST, FIRST MIDDLE into FIRST MIDDLE LAST

formula 2: =LEFT(F2,FIND(" ",F2))&TRIM(RIGHT(SUBSTITUTE(F2," ",REPT(" ",99)),99))  <-- this turns FIRST MIDDLE LAST into FIRST LAST

thanks for your help!

1 Solution

Accepted Solutions
punam_wagh
New Contributor III

Re: is there a function that can act like "find" in excel that i can substitute into qlikview?

You can also try this,

=subfield(Fieldname,',',2) &' '&subfield(Fieldname,',',1) ---convert Last, First Middle into First middle last

=subfield(trim(subfield(Fieldname,',',2)),' ',1)&' '&subfield(Fieldname,',',1)-- convert Last, First Middle into first last

View solution in original post

3 Replies
Partner
Partner

Re: is there a function that can act like "find" in excel that i can substitute into qlikview?

Take a look at the INDEX() function.

punam_wagh
New Contributor III

Re: is there a function that can act like "find" in excel that i can substitute into qlikview?

You can also try this,

=subfield(Fieldname,',',2) &' '&subfield(Fieldname,',',1) ---convert Last, First Middle into First middle last

=subfield(trim(subfield(Fieldname,',',2)),' ',1)&' '&subfield(Fieldname,',',1)-- convert Last, First Middle into first last

View solution in original post

pgbehles1
New Contributor

Re: is there a function that can act like "find" in excel that i can substitute into qlikview?

the second formula worked perfectly! thank you so much!