Problem on Outer Join

[#1]

Hello everyone,

I'm facing an issue on an outer join. I have the following script :

[mapProcess]:

Mapping LOAD

    idProcess,

    processName

FROM [lib://QVD EXTRACT/BITRACA_PROCESS.qvd]

(qvd);

[BRAKE]:

LOAD

    brk_productIdentifier,

    brk_idLine,

    brk_idReference,

    brk_manufacturingDate,

    ApplyMap('mapProcess', "brk_idProcess") as brk_process,

    brk_processRecord,

    brk_processDate,

    brk_componentLabel as comp_productLabel

FROM [lib://QVD EXTRACT/BITRACA_MANUF.qvd]

(qvd);

[FACT]:

LOAD

    comp_productLabel,

    comp_componentLabel,

    comp_preparationNumber,

    comp_idPlant,

    comp_idOperation,

    comp_operationDate,

    comp_partNumber,

    comp_quantity,

    comp_supplier,

    comp_batch

FROM [lib://QVD EXTRACT/BITRACA_COMP.qvd]

(qvd);

JOIN([FACT])

LOAD * RESIDENT [BRAKE];

STORE * from FACT INTO [LIB://QVD TRANSFO/FACT.qvd](qvd);

Drop table BRAKE;

Drop table [FACT];

The objective is to get one table with those columns :

    brk_productIdentifier,

    brk_idLine,

    brk_idReference,

    brk_manufacturingDate,

    brk_process,

    brk_processRecord,

    brk_processDate,

    comp_productLabel

    comp_componentLabel,

    comp_preparationNumber,

    comp_idPlant,

    comp_idOperation,

    comp_operationDate,

    comp_partNumber,

    comp_quantity,

    comp_supplier,

    comp_batch

But when opening the QVD file, it seems to be full of empty data as you can see in the following screenshot.

Any ideas on how can I get this work ?

[jonathandienst]

Are you sure that the comp_productLabel values are common to both tables? And that they are the same type? Your script looks OK, but perhaps the data is not. Are you sure that you want or need an outer join?

[martinpohl]

Do the first lines in your Fact-table have equal comp_ProductLabel values?

I would check the data by loading both tables without join (so you have a linked field comp_ProductLabel) and create a table with values of both tables.

Regards

[#4]

Here is an example of the productLabel I have in both tables :

BRAKE : 

    brk_productIdentifier : 027028

    brk_idLine : 027

    brk_idReference : 020470

    brk_manufacturingDate : 2017-07-11

    brk_process : INSERT.Leak

    brk_processRecord : 3

    brk_processDate : 2017-07-10

    comp_productLabel : 152

COMP :     

    comp_productLabel : 152

    comp_componentLabel : 164

    comp_preparationNumber : 8037

    comp_idPlant : 634

    comp_idOperation : ...

    comp_operationDate : ...

    ...

And what I want to get

FACT :

            brk_productIdentifier : 027028

    brk_idLine : 027

    ...

    comp_productLabel : 152

    comp_componentLabel : 164

    ...

I don't care if there are NULL values in some fields, so it does looks like an outer join, doesn't it ?

(I can have NULL values in comp_productLabel in the table BRAKE could this be the reason ?)

[#5]

No, they doesn't. the first 171900 lines of the first fact table have a NULL value for productLabel. Is this the reason why the outer join doesn't work ?

[jonathandienst]

The records with Null value will load, but the Null values cannot join to each other. With so many records containing nulls, your join could not be expected to really work well.