Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
just
Table B
load
left(Family_Code,2) as Familiy_Code
then left join(Table),
Family_Code,
Product
resident TableB
..
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
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
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 & '*';
Hi
You will need 3 steps here.
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
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;