topic Re: Working with strings in QlikView

Working with strings

Tue, 13 Jun 2017 14:13:13 GMT

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

Re: Working with strings

Anonymous — Tue, 13 Jun 2017 17:11:21 GMT

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

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

Re: Working with strings

passionate — Tue, 13 Jun 2017 17:25:46 GMT

Hi,

You can use:

1. Applymap

2. Replace Function

3. Pick Match Function

Regards,

Pankaj

Re: Working with strings

Anonymous — Tue, 13 Jun 2017 17:36:29 GMT

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) & '.'

Re: Working with strings

Tue, 27 Jun 2017 11:40:10 GMT

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

Today I will try your solution.

Regards,

Laura

Re: Working with strings

Tue, 27 Jun 2017 11:41:15 GMT

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

Today I will try your solution.

Regards,

Laura

Re: Working with strings

Tue, 27 Jun 2017 11:42:59 GMT

Thanks a lot, very usefull!.

I am really stressed with this.

Re: Working with strings

Tue, 04 Jul 2017 21:47:10 GMT

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

Re: Working with strings

Anonymous — Wed, 05 Jul 2017 16:13:59 GMT

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.

Re: Working with strings

BalaBhaskar_Qlik — Wed, 05 Jul 2017 16:59:24 GMT

UserNameMap:

Mapping LOAD * INLINE

[

ShortName, LongName

ES, Spain

US, United States

];

MAP CountryCode USING UserNameMap;

Data:

LOAD * INLINE [

CountryCode

];

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);

Re: Working with strings

Thu, 06 Jul 2017 07:17:17 GMT

Thank you very much.

I have tried this and it works!

SubField([Employees1],' ',1)&' '&LEFT(SubField([Employees1],' ',2),1)&'.' AS [Employees2]

Continue ...

Re: Working with strings

Anonymous — Tue, 11 Jul 2017 21:59:09 GMT

If someone answered your question please close the thread.

Qlik Community Tip: Marking Replies as Correct or Helpful