topic Re: Left Join not show all data in App Development

Left Join not show all data

Bukan4x4 — Mon, 20 Feb 2023 16:24:52 GMT

Hello all, I have a issue with left join in my load data script. I have 2 tables like those:

TABLE A

Project BB don't have subproject

TABLE B

I need join both tables for get this:

This is my script:

LEFT Join TABLE B
Load
project, subproject, taxes

Resident TABLE A;
DROP TABLE TABLE A;

And I get this:

No "taxes" join to the table for all "project" without "subproject".

Can anyone show me where is the error please?

Best regards

ckarras22 — Mon, 20 Feb 2023 18:13:57 GMT

Hello,

before your 2 tables you can write:

NullAsValue subproject;

vinieme12 — Wed, 22 Feb 2023 07:39:47 GMT

I would suggest creating a composite key yourself for joining the tables

A:
Load project,subproject,project&'-'&subproject as KEYFIELD,taxes inline [
project,subproject,taxes
AA,11,0.1
AA,22,0.2
BB,,0.3
];

Left JOin (A)
Load project&'-'&subproject as KEYFIELD,sales inline [
project,subproject,sales
AA,11,4
AA,22,5
BB,,2
];

exit Script;

Bukan4x4 — Fri, 03 Mar 2023 19:51:46 GMT

Thanks to all for yours ideas, Finally I did this:

IF (len(subproject)='0','NA',subproject) as subproject

Works Perfect!