Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Read about the latest Qlik Community enhancements on the Community News blog!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Problem on Outer Join

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.

Capture.PNG

Any ideas on how can I get this work ?

5 Replies
jonathandienst
Partner
Partner

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?

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
martinpohl
Partner
Partner

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

Not applicable
Author

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 ?)

Not applicable
Author

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
Partner
Partner

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein