10 Replies Latest reply: Sep 3, 2013 4:13 AM by Stefan Wühl RSS

    Problem with Join

    Staffan Johansson

      I have a problem to do a join, my problem is this:

      left_Join.png

      invoiced amount is -56200, but this invoice has been payed at two different times. So when I do a left Join it will duplicate the row with amount -56200. I have try to sum(Supplier Pay_Amount_LOCAL) and group by on Invoice No before i Make my Join, but without result, what i'm I missing here?

       

      //Stabben

        • Re: Problem with Join
          Stefan Wühl

          How does the structure of your original tables look like? Could you post your script code snippet?

          (Or even better, a small sample app with some sample data).

            • Re: Problem with Join
              Staffan Johansson

              Temp:

              Load

              (EPSINO &'|'&EPDIVI&'|'&EPSPYN&'|'&EPSUNO) as %SINOKey ,
              (
              EPCUAM*EPARAT) as [Supplier Amount_LOCAL] ,
              (
              EPCUAM) as [Supplier Amount_FOREIGN],
              EPCUCD  as [Supplier Currency] ,
              EPARAT  as [Supplier Exchangerate] ,
              From $(vQvdPath)FPLEDG.qvd (qvd)
              Where EPTRCD=40

              ;
              left Join (Temp)
              Load
              TEXT(EPSINO &'|'&EPDIVI&'|'&EPSPYN&'|'&EPSUNO) as %SINOKey,
              (
              EPCUAM * EPARAT) as [Supplier Pay_Amount_LOCAL]
              From $(vQvdPath)FPLEDG.qvd (qvd)
              Where EPTRCD=50
              ;

               

              First I Load with filter EPTRCD=40 and yhis has one row with -56200

              then I join and get two rows because i have two different amount with filter EPTRCD=50

              //Stabben

                • Re: Problem with Join
                  Stefan Wühl

                  A join may not be appropriate then. Why do you need to join?

                   

                  You could consider keeping the Supplier_Amount_LOCAL value in a separate table just linked via InvoiceNo.

                  Or maybe create a concatenated fact table with different levels of granularity (total Supplier_Amount_LOCAL value and your detailed transactions).

                   

                  Hard to say what you need to do, because data modelling should consider your complete requirements.

                    • Re: Problem with Join
                      Staffan Johansson

                      If i just keep the tables assosiated i will still be two lines and two amount with -56200. if I sum all transactions on this invoice it should be 0, but it doesen't. I do need this fields in the same fact table on the same aggregated level, for ex Invoice No. The whole modell is about if the supplier are due or not or if they have payed the whole amount.

                       

                      Thanks for help

                       

                      //stabben

                        • Re: Re: Problem with Join
                          Stefan Wühl

                          If i just keep the tables assosiated i will still be two lines and two amount with -56200. if I sum all transactions on this invoice it should be 0, but it doesen't. I do need this fields in the same fact table on the same aggregated level, for ex Invoice No. The whole modell is about if the supplier are due or not or if they have payed the whole amount.

                           

                           

                          Hm, I think it should work with the linked tables. Maybe your data model is different than I am assuming. Please note that the way you aggregate may influence the results (i.e. just aggregating fields from the one linked SupplierAmountLocal table should only aggregate the one line).

                           

                          Here is an example of both methods, first using a linked table, second a concatenated fact table:

                           

                          OUT:

                          LOAD * INLINE [

                          InvoiceNo, SupplierAmountLocal

                          90520783, -56200

                          ];

                           

                          IN:

                          LOAD * INLINE [

                          InvoiceNo, SupplierPayAmountLocal, Date,

                          90520783, 21515, 2013-09-01

                          90520783, 34685, 2013-09-02

                          ];

                           

                          CONCAT:

                          LOAD InvoiceNo,

                              SupplierAmountLocal as Value,

                              'OUT' as DIR

                          Resident OUT;

                           

                          CONCATENATE

                          LOAD InvoiceNo,

                              SupplierPayAmountLocal as Value,

                              Date as Date2,

                              'IN' as DIR

                          Resident IN;

                           

                          Doing the aggregation not in the load script has the advantage that you don't lose details (i.e. the single date balances).

                           

                          Check attached.

                      • Re: Problem with Join
                        Iyyappan v

                        Hi,

                         

                        If u are using the composite key use autonumber function using the both tables.

                        autonumber(EPSINO&EPDIVI&EPSPYN&EPSUNO) as %SINOKey,


                        Autonumber:
                        Returns a unique integer value for each distinct evaluated value of expression encountered during the script execution

                         

                        Regards,

                         


                    • Re: Problem with Join
                      Clever Anjos

                      If you use any type of JOIN, records from first table will be repeated for each matching record of second.

                      I can see two approaches:

                      1) Keep them separated

                      2) Concatenate both tables and then run a LOAD () GROUP BY doing your sum´s

                        • Re: Problem with Join
                          Clever Anjos

                          Second way would be like this:

                           

                          Temp:

                          Load

                          (EPSINO &'|'&EPDIVI&'|'&EPSPYN&'|'&EPSUNO) as %SINOKey ,

                          (EPCUAM*EPARAT) as [Supplier Amount_LOCAL] ,

                          (EPCUAM) as [Supplier Amount_FOREIGN],

                          EPCUCD  as [Supplier Currency] ,

                          EPARAT  as [Supplier Exchangerate] ,

                          From $(vQvdPath)FPLEDG.qvd (qvd)

                          Where EPTRCD=40

                          ;

                          Concatenate (Temp)

                          Load

                          TEXT(EPSINO &'|'&EPDIVI&'|'&EPSPYN&'|'&EPSUNO) as %SINOKey,

                          (EPCUAM * EPARAT) as [Supplier Pay_Amount_LOCAL]

                          From $(vQvdPath)FPLEDG.qvd (qvd)

                          Where EPTRCD=50;

                           

                           

                          Final:

                          LOAD

                            %SINOKey,

                            sum([Supplier Amount_LOCAL]) as [Supplier Amount_LOCAL],

                            sum([Supplier Amount_FOREIGN]) as [Supplier Amount_FOREIGN],

                            sum([Supplier Currency] ) as [Supplier Currency],

                            sum([Supplier Exchangerate]) as [Supplier Exchangerate],

                            sum([Supplier Pay_Amount_LOCAL]) as [Supplier Pay_Amount_LOCAL]

                          Resident Temp

                          Group By %SINOKey;

                          Drop Table Temp;