Working with strings

[#1]

Hello, I need help with this:

I have two excel files but NO common field (file 1 and file 2)

I need to see how identify the employees1 of file 1 (Garcia Fernandez Luis) with the employees2 of the other file 2 (Luis G.) In File 1

Finally I have to replace the field containing (Garcia Fernandez Luis) for file 2 field (Luis G.)

Can you give me some idea? I'm very lost. Thank you

[Anonymous]

You could look into doing it in your script with this:

http://www.qlikfix.com/2010/10/18/regular-expressions-in-the-load-script/

[passionate]

Hi,

You can use:

1. Applymap

2. Replace Function

3. Pick Match Function

Regards,

Pankaj

[Anonymous]

Depending on the consistency in your data you could just try this on your first file.

subfield(Emplyees1,' ',substringcount(Employees1,' ')+1) * ' ' & left(subfield(Emplyees1,' ',1),1) & '.'

[#5]

Thanks a lot. I have been out for a week.

Today I will try your solution.

Regards,

Laura

[#6]

Thaks a lot. I have been out for a week.

Today I will try your solution.

Regards,

Laura

[#7]

Thanks a lot, very usefull!.

I am really stressed with this.

[#8]

Hello, I have tried to use it but it doesn`t work. I am not sure I don`t understand where to use Employees2 because finally it must replace Employees1

Thank a lot

[Anonymous]

You essentially have to create a field that matches.

So it would look something like (You probably have more fields to include from each file):

Table1:

Load

subfield(Emplyees1,' ',substringcount(Employees1,' ')+1) * ' ' & left(subfield(Emplyees1,' ',1),1) & '.' as Employees2

From

File1.xlsx

(ooxml, no labels, table is Sheet1);

Table2:

Load

Emplyees2

from File2.xlsx

(ooxml, no labels, table is Sheet1);

This way you have created the matching field from File1 that will connect with the data in File2.

[balabhaskarqlik]

UserNameMap: 

Mapping LOAD * INLINE

[ 

ShortName, LongName 

ES, Spain 

US, United States 

]; 

MAP CountryCode USING UserNameMap; 

Data: 

LOAD * INLINE [ 

CountryCode 

ES 

UK 

US 

]; 

or

DataMap:

Mapping load * inline

[

x, y

Sales, ''

Order, ''

Text,''

];

NewTab:

LOAD

Col1,

Col2,

mapsubstring('DataMap',String) as String

FROM [abc.xlsx](ooxml, embedded labels, table is Sheet1);