5 Replies Latest reply: Aug 2, 2017 5:26 AM by Jonathan Dienst RSS

    Problem on Outer Join

    Axel Galliot

      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 ?

        • Re: Problem on Outer Join
          Jonathan Dienst

          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?

            • Re: Problem on Outer Join
              Axel Galliot

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

            • Re: Problem on Outer Join
              Martin Pohl

              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