Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
mandilicious
Creator II
Creator II

How to add space between first name and last name

Hi Champs

Can someone help on how can I add space between First & last name in below example table?

Space.PNG

I need to have space  like below

Brolin Helen

Callins Joan

Carsson Rob etc.

Thanks

1 Solution

Accepted Solutions
sunny_talwar

Try this

Table:

LOAD *,

Left(SalesPersons, FindOneOf(SalesPersons, 'ABCDEFGHIJKLMNOPQRSTUVWXYZ', 2)-1) & ' ' & Mid(SalesPersons, FindOneOf(SalesPersons, 'ABCDEFGHIJKLMNOPQRSTUVWXYZ', 2)) as NewSalesPersons;

LOAD * INLINE [

    SalesPersons

    BrolinHelen

    CallinsJoan

    CarssonRob

];

Capture.PNG

View solution in original post

12 Replies
sunny_talwar

Try this

Table:

LOAD *,

Left(SalesPersons, FindOneOf(SalesPersons, 'ABCDEFGHIJKLMNOPQRSTUVWXYZ', 2)-1) & ' ' & Mid(SalesPersons, FindOneOf(SalesPersons, 'ABCDEFGHIJKLMNOPQRSTUVWXYZ', 2)) as NewSalesPersons;

LOAD * INLINE [

    SalesPersons

    BrolinHelen

    CallinsJoan

    CarssonRob

];

Capture.PNG

mandilicious
Creator II
Creator II
Author

Hi Sunny

Thanks you very much I worked , maybe I am asking obvious question can you please explain why u used ?

1) 'ABCDEFGHIJKLMNOPQRSTUVWXYZ' ?

2) ', 2)-1) and , 2)) ?

Thanks

sunny_talwar

1) 'ABCDEFGHIJKLMNOPQRSTUVWXYZ' ?

These are used as the second argument for the FindFirstOf function which will return the position of the 1st (or 2nd in this case) of the upper case letters ABCD... based on the third argument.

2) ', 2)-1) and , 2)) ?

2 is the third argument... I am looking to find the beginning of the last name which always seems to be begin with a upper case letter. -1 is for the left function... Essentially pick everything before the 2nd upper case letter.

Does that make sense?

mandilicious
Creator II
Creator II
Author

Yes it does.

  Thanks

sunny_talwar

Super

mandilicious
Creator II
Creator II
Author

Hi Sunny

Quick one actually its related to the above question but differ little bit.i have table as below but i need to separate them like to have only Month(Jul,May,Oct,Sep etc ) and year(2015 and 2016)

Company:

LOAD Field,

     [Jul-Sep 2015],

     [May-Aug 2015],

     [Oct-Dec 2015],

     [Jan-Mar 2016]

from

C:\Users..........

Thanks

sunny_talwar

Are you doing a cross table load on your data?

mandilicious
Creator II
Creator II
Author

No i was not doing cross table.

sunny_talwar

Can you share few rows of data with the expected output from it?

Best,

Sunny