topic Re: concatenate Pharmacy names ,space creating problem in QlikView

concatenate Pharmacy names ,space creating problem

Anonymous — Thu, 18 Jun 2015 07:22:25 GMT

hi gurus,

i have pharmacy name:

Caremark P.R. Specialty Pharmacy

i want to concatenate into single name,but there is space difference between two pharmacy names

plz find the sample application

Re: concatenate Pharmacy names ,space creating problem

ajaykumar1 — Thu, 18 Jun 2015 07:25:37 GMT

Hi,

Whats your expecting output?

Regards,

Ajay

Re: concatenate Pharmacy names ,space creating problem

Anonymous — Thu, 18 Jun 2015 07:28:52 GMT

names are similar so i want single

output:

Caremark P.R. Specialty Pharmacy

Re: concatenate Pharmacy names ,space creating problem

fvelascog72 — Thu, 18 Jun 2015 07:31:00 GMT

Hi,

Try this:

DATA:

LOAD

Replace(Name,' ',' ')

Inline [

Name

Caremark P.R. Specialty Pharmacy

];

Re: concatenate Pharmacy names ,space creating problem

antoniotiman — Thu, 18 Jun 2015 07:31:07 GMT

If Your problem is only 2 spaces instead 1, You can use

Replace([Pharmacy Name],' ', ' ') ('2 spaces','1 space')

Regards,

Antonio

Re: concatenate Pharmacy names ,space creating problem

ajaykumar1 — Thu, 18 Jun 2015 07:36:33 GMT

Try this;

But not sure

=if([Pharmacy name]='Caremark P.R. Specialty Pharmacy',(replace([Pharmacy name],'Caremark P.R. Specialty Pharmacy','Caremark P.R. Specialty Pharmacy')))

Regarrds,

Ajay

Re: concatenate Pharmacy names ,space creating problem

Anonymous — Thu, 18 Jun 2015 07:51:37 GMT

but i have 100 pharmacy names with typo errors .

can i use mix match like that

thanks

Re: concatenate Pharmacy names ,space creating problem

tamilarasu — Thu, 18 Jun 2015 07:56:13 GMT

Hi,

Try like below,

Table1:
LOAD  Replace(Trim([Pharmacy name]),'  ', '') as [Pharmacy name]
FROM
concate.xlsx
(ooxml, embedded labels, table is Sheet1);
concatenate(Table1)

LOAD  Replace(Trim([Pharmacy name]),'  ', '') as [Pharmacy name]
FROM
concate.xlsx
(ooxml, embedded labels, table is Sheet2);

Re: concatenate Pharmacy names ,space creating problem

jagan — Thu, 18 Jun 2015 07:57:51 GMT

Hi,

Use this script

Table1:

LOAD [Pharmacy name],

Replace([Pharmacy name], ' ', ' ') AS Pharmacy_Name_Formatted

FROM

concate.xlsx

(ooxml, embedded labels, table is Sheet1);

concatenate(Table1)

LOAD [Pharmacy name],

Replace([Pharmacy name], ' ', ' ') AS Pharmacy_Name_Formatted

FROM

concate.xlsx

(ooxml, embedded labels, table is Sheet2);

Regards,

Jagan.

Re: concatenate Pharmacy names ,space creating problem

Anonymous — Thu, 18 Jun 2015 09:06:48 GMT

hi all,

Thanks for your valuable time

i have like

TOPS - Texas Oncology Pharmacy Services

TOPS Texas Oncology Pharmacy Services

like this i have 10 to 15 names with Minor changes .

is there any dynamic way to solve

this problem

Re: concatenate Pharmacy names ,space creating problem

fvelascog72 — Thu, 18 Jun 2015 09:10:29 GMT

Hi,

Try this:

Replace(PurgeChar(Name,'-'),' ',' ')

Re: concatenate Pharmacy names ,space creating problem

jagan — Thu, 18 Jun 2015 09:13:04 GMT

Hi,

For this type of scenarios you have to maintain a mapping file like below

PharmacyName, PharmacyNameGroup

TOPS - Texas Oncology Pharmacy Services, TOPS - Texas Oncology Pharmacy Services

TOPS Texas Oncology Pharmacy Services, TOPS - Texas Oncology Pharmacy Services

Caremark P.R. Specialty Pharmacy, Caremark P.R. Specialty Pharmacy

Caremark P.R. Specialty Pharmacy,Caremark P.R. Specialty Pharmacy

Now you can use PharmacyNameGroup in the list box. Otherwise it is very difficult to handle.

Regards,

Jagan.