Problem with Join

[stabben23]

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

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

[swuehl]

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.