Left Join with Where statement

[#1]

Hi,

I have a problem.

I have two tables like this :

FindCategory:

Load * Inline
[
Code, Category
A005, Regroup1
DRAP, Regroup2

A017, Regroup1
];

MyDataBase:

Load
[Name Number] as Designation,
Art,
Text(SN) as SN,
Code
FROM

(biff, embedded labels, table is Format$);

The field named "Code" from MyDataBase contain values :

701_A005_789
966_DRAP_789

966_A005_789

...

I Want to find the Categorie from Code but inside the full code :

701_A005_789 Categorie is = Regroup1

701_DRAP_789 Categorie is = Regroup2

I've tried this but it's not working :

Left Join(FindCategory)
Load *
Resident MyDataBase

Where WildMatch(MyDataBase.Code, '*' + FindCategory.Code + '*');

Could you help me?

[jonathandienst]

Hi

So what is your question?

Jonathan

[#3]

So what is your problem? Please explain.

Vinay

[Aurelien_Martinez]

Hi,

MAPPING

FindCategory_MAP:

Load * Inline
[
Code, Category
A005, Regroup1
DRAP, Regroup2

A017, Regroup1
];

MyDataBase:

Load
[Name Number] as Designation,
Art,
Text(SN) as SN,
Code,

applymap('FindCategory_MAP', mid(Code, 5, 4)) as Category
FROM

(biff, embedded labels, table is Format$);

[#5]

Thank for your reply.

I understand this line : applymap('FindCategory_MAP', mid(Code, 5, 4)) as Category

but imagine that my Code is not formated like : 701_A005_789 but like 70000XXX1_A005_789?

Is it possible to use a like statement? applymap('FindCategory_MAP', *Code*) as Category

[Aurelien_Martinez]

Peraps with :

applymap('FindCategory_MAP', SubField(Code, '_', 2)) as Category

[maxgro]

RESULT

if you want to add the Category

using some test data for MyDataBase

SCRIPT

FindCategory:

Mapping Load * Inline

[

Code, Category

A005, Regroup1

DRAP, Regroup2

A017, Regroup1

];

MyDataBase:

Load * Inline [

Designation, Art, SN, Code

1,1,1,701_A005_789

2,2,2,701_A005_790

3,3,3,966_DRAP_789

4,4,4,966_A005_789

];

Table:

NoConcatenate load

  *,

  ApplyMap('FindCategory', SubField(Code, '_', 2)) as Category

Resident

  MyDataBase;

DROP Table MyDataBase;

[maxgro]

if you want to load only records from MyDataBase with category in FindCategory

FindCategory:

Load * Inline

[

Code1, Category

A005, Regroup1

DRAP, Regroup2

A017, Regroup1

];

MyDataBase:

Load * Inline [

Designation, Art, SN, Code

1,1,1,701_A005_789

2,2,2,701_A005_790

3,3,3,966_DRAP_789

4,4,4,966_A005_789

5,5,5,966_A006_789

6,6,6,966_A007_789

];

Table:

NoConcatenate load

  *

Resident

  MyDataBase

Where Exists(Code1, SubField(Code, '_', 2)) ;

DROP Table MyDataBase;