topic Re: How to add space between first name and last name in QlikView

How to add space between first name and last name

mandilicious — Wed, 25 Nov 2020 16:16:04 GMT

Hi Champs

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

I need to have space like below

Brolin Helen

Callins Joan

Carsson Rob etc.

Thanks

Re: How to add space between first name and last name

sunny_talwar — Thu, 19 Jul 2018 13:08:28 GMT

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

];

Re: How to add space between first name and last name

mandilicious — Thu, 19 Jul 2018 13:21:06 GMT

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

Re: How to add space between first name and last name

sunny_talwar — Thu, 19 Jul 2018 13:25:11 GMT

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?

Re: How to add space between first name and last name

mandilicious — Thu, 19 Jul 2018 13:33:21 GMT

Yes it does.

Thanks

Re: How to add space between first name and last name

sunny_talwar — Thu, 19 Jul 2018 13:34:00 GMT

Super

Re: How to add space between first name and last name

mandilicious — Fri, 20 Jul 2018 09:43:12 GMT

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

Re: How to add space between first name and last name

sunny_talwar — Fri, 20 Jul 2018 11:20:10 GMT

Are you doing a cross table load on your data?

Re: How to add space between first name and last name

mandilicious — Fri, 20 Jul 2018 13:16:37 GMT

No i was not doing cross table.

Re: How to add space between first name and last name

sunny_talwar — Fri, 20 Jul 2018 13:21:14 GMT

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

Best,

Sunny

Re: How to add space between first name and last name

mandilicious — Fri, 20 Jul 2018 13:36:23 GMT

Hi Sunny

I need to separate it like below

Month

JUL

OCT

JAN

APR

YEAR

2016

2015

Thanks

Re: How to add space between first name and last name

sunny_talwar — Fri, 20 Jul 2018 13:46:33 GMT

Try this

Table:

CrossTable(MonthYear, Data)

LOAD Country,

" [Jul-Sep 2015]",

" [May-Aug 2015]",

" [Oct-Dec 2015]",

" [Jan-Mar 2016]"

FROM

[..\..\Downloads\Exp (1).xlsx]

(ooxml, embedded labels, table is Sheet1);

NewTable:

LOAD *,

Date(MakeDate(Year, Month)) as MonthYear;

LOAD Country,

Month(Date#(Left(Trim(PurgeChar(MonthYear, ']['&Chr(160))), 3), 'MMM')) as Month,

Year(Date#(Right(Trim(PurgeChar(MonthYear, ']['&Chr(160))), 4), 'YYYY')) as Year,

Data

Resident Table;

DROP Table Table;

Re: How to add space between first name and last name

mandilicious — Fri, 20 Jul 2018 13:56:37 GMT

Thank you very much Sunny you are the best:)