topic Re: Left Join with Condition in QlikView

Left Join with Condition

Mon, 06 Oct 2014 11:01:46 GMT

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

(...)

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

Re: Left Join with Condition

Anonymous — Mon, 06 Oct 2014 11:05:02 GMT

just

Table B

load

left(Family_Code,2) as Familiy_Code

then left join(Table),

Family_Code,

Product

resident TableB

Re: Left Join with Condition

Mon, 06 Oct 2014 11:16:12 GMT

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

Re: Left Join with Condition

Mon, 06 Oct 2014 11:22:43 GMT

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

Re: Left Join with Condition

anbu1984 — Mon, 06 Oct 2014 11:31:40 GMT

Try this

TableA:

lOAD *,1 As JnKey Inline [

Family_CodeA

];

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 & '*';

Re: Left Join with Condition

jonathandienst — Mon, 06 Oct 2014 11:35:06 GMT

You will need 3 steps here.

First load table A (and a temp field containing the first character) into a temp table.
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.
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

Re: Left Join with Condition

Mon, 06 Oct 2014 12:11:02 GMT

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;