Left Join with Condition

[#1]

Hello,

I need to do a left join to 2 tables (resident and QVD), where the first has the partial beginning of Family Code.

Table A (Resident table, example):

Family_Code

A1

B2

(...)

Table B (QVD table, example):

Family_Code     Product

A111                   AAA

B233                   BBB

A123                   AAA1

B245                    BBB2

A236                    AAA2

B123                    BBB4

(...)

Wanted Final Table (Left Join of the tables)

Product

AAA

BBB

AAA1

BBB2

Where the join is something like -  mid(B.Family_Code, 1, len(A.Family_Code))=A.Family_Code

Any sugestions?

Thanks is advance,

Francisco

[Anonymous]

just

Table B

load

left(Family_Code,2) as Familiy_Code

then left join(Table),

Family_Code,

Product

resident TableB

..

[#3]

Thanks Rudolf.

The len of Family_Code in Table A, can vary from 1, to max len of Family_Code in Table B...

Thats why I need something like mid(B.Family_Code, 1, len(A.Family_Code))=A.Family_Code

Regards,

Francisco

[#4]

The len of Family_Code in Table A, can vary from 1, to max len of Family_Code in Table B...

Thats why I need something like mid(B.Family_Code, 1, len(A.Family_Code))=A.Family_Code

Regards,

Francisco

[anbu1984]

Try this

TableA:

lOAD *,1 As JnKey Inline [

Family_CodeA

A1

B2

];

Join

lOAD *,1 As JnKey  Inline [

Family_Code,Product

A111,AAA

B233,BBB

A123,AAA1

B245, BBB2

A236, AAA2

B123, BBB4

B233,AAAA ];

NoConcatenate

Final:

Load Product Resident TableA where Family_Code Like Family_CodeA & '*';

[jonathandienst]

Hi

You will need 3 steps here.

1. First load table A (and a temp field containing the first character) into a temp table.
2. Now join TableB (join on the temp field T_Family_Code). This is to reduce the number of unneeded rows that a cross join would generate - which will be a problem if TableA and/or TableB are large. The temp table has all the relevant content from both source tables.
3. Now reload the temp table into table Result, filtering on the expression Index(Family_Code_B, Family_Code_A) = 1

The code looks like this:

T_TableA:

LOAD Family_Code As Family_Code_A,

  Left(Family_Code, 1) As T_Family_Code

FROM tableA.qvd;

Left Join(T_TableA)

LOAD Left(Family_Code, 1) As T_Family_Code

  Family_Code As Family_Code_B,

  Product

FROM tableB.qvd;

Result:

LOAD Family_Code_A As FamilyGroup,

  Family_Code_B As Family_Code,

  Product

FROM T_TableA

WHERE Index(Family_Code_B, Family_Code_A) = 1

DROP T_TableA;

HTH

Jonathan

[#7]

Try this..

Final_Table:

LOAD

Family_Code as Family_Key

Resident A;

Left Join

LOAD

Left(Family_Code,2) as Family_Key,

Family_Code,

Product

Resident  B;