topic Concatenate fields in QlikView

Concatenate fields

Tue, 16 Jun 2015 16:49:38 GMT

Hi All I need to Concatenate the below fields and create one field but in here we are not having Middle name fro all the name and now if middle name doe not exist i need to show it in this way

F_Name, L_Name by excluding the middlename field

by using the below script it is showing like this

F_Name, , L_Name if middle name doe not exist.

[F_Name]&', '& M_Name &', '& [L_Name] AS [Total Name]

can anyone help me in excluding the '', ,'' when there is no middle name.

Re: Concatenate fields

Anonymous — Tue, 16 Jun 2015 16:52:26 GMT

Hi,

you can use If statment, IF Middlename is null (or empty) you concatenate only Firstname and Lastname else you concatenate Firstname, Middlename (with commas) and Lastname.

Re: Concatenate fields

Tue, 16 Jun 2015 16:53:27 GMT

also need to have the first letter in first name in uppercase and rest of them in lower case

similarly for the Middle name and Last name

Re: Concatenate fields

Qrishna — Tue, 16 Jun 2015 16:54:14 GMT

Hi Chinna,

Try

[F_Name] &', '& If(len(trim(M_Name))>0,M_Name,' ') &', '& [L_Name] AS [Total Name]

Thanks

krishna

Re: Concatenate fields

Tue, 16 Jun 2015 16:54:15 GMT

Can you give me the script I am unable to build it

Re: Concatenate fields

Qrishna — Tue, 16 Jun 2015 16:55:47 GMT

Try

Capitalise([F_Name]) &', '& If(len(trim(M_Name))>0,Capitalise(M_Name),' ') &', '& Capitalise([L_Name]) AS [Total Name]

Re: Concatenate fields

sinanozdemir — Tue, 16 Jun 2015 16:55:54 GMT

How about something like this:

=If(M_Name <> '', F_Name & ', ' & L_Name, F_Name & ', ' & M_Name & ', ' L_Name)

Re: Concatenate fields

sunny_talwar — Tue, 16 Jun 2015 16:57:03 GMT

for use capitalize() function for that

May be like this:

Capitalized(F_Name) & If(Len(Trim(M_Name)) <> 0, ', ' & M_Name) & ', ' & L_Name as [Total Name]

Re: Concatenate fields

Anonymous — Tue, 16 Jun 2015 17:29:20 GMT

Hi,

sorry, other have do this before me! 🙂

You can use capitalize, if the text contain other words that you dont want to capitalize you can use left+mid and concatenate:

upper(left(firstname,1))&mid(firstname,2,len(firstname)-1)

Regards.

Re: Concatenate fields

Tue, 16 Jun 2015 17:36:54 GMT

thank you all for the wonderful help!

really appreciate your help...........