how to join many payments related to the same bill

[#1]

Hello! I need you help with this problem. I need a field in Sales Table which indicates the sum of payment amount related to the bill.

The field ID identify the bill, while there could be many PaymentID related to the same ID

I tryed left join  but it doesn't work because there are many PaymentID related to the same ID

Thank you very much for your help!!

I attached the qvf and example file

I have the following script:

Sales:

LOAD

    ID,

    "Date",

    DueDate,

    Amount

FROM [lib://Documents/Joining payments to bills.xlsx]

(ooxml, embedded labels, table is Sales);

left join (Sales)

Payments:

LOAD

    PaymentID,

    ID,

    "Date",

    PaymentAmount

FROM [lib://Documents/Joining payments to bills.xlsx]

(ooxml, embedded labels, table is Payments);

[sunny_talwar]

What is the expected output after you join the two tables?

[#3]

Hello Sunny T, sorry if I wasn't clear.

The purpose of getting the field  Sum of PaymentAmount in Sales Table is to filter the debt of each document (Sum Amount- Payment Amount), and the antiquity of the debt. If I don't join the tables, I could  know the amount of deb but cannot classify it according to the antiquity

[swuehl · Accepted solution]

Either just keep the two tables linked (renaming the Date field to avoid an unwanted link by Date) or aggregate your data before JOINing:

Sales:

LOAD

    ID,

    "Date",

    DueDate,

    Amount

FROM [lib://Documents/Joining payments to bills.xlsx]

(ooxml, embedded labels, table is Sales);

left join (Sales)

LOAD

   // PaymentID,

    ID,

//    "Date",

  Sum( PaymentAmount ) as SummedPayments

FROM [lib://Documents/Joining payments to bills.xlsx]

(ooxml, embedded labels, table is Payments)

GROUP BY ID;

[#5]

Hello! Thanks, it works well when connecting with an excel file, but it doesn't with a sql connection. do you know what can be wrong? thank you very much

[BI_Comprobantesx3]:

LOAD

  [FechaComprobante] AS [Fecha],

  [FechaPrimerVencimiento] AS [Vencimiento],

  [CondiciondePago] AS [Condición Pago],

  [CUIT] AS [CUIT],

  [UnidaddeNegocio] AS [Negocio],

  [Total],

  ID,

  Date([FechaComprobante]) AS [FechaComprobante],

    'Facturación' as [Tipo Comprobante],

    if([FechaPrimerVencimiento]>date(today()),'Vencido','Sin vencer') as [Estado],

  [UsuariaLinkeada] AS [NroCliente-Usuaria];

SQL SELECT "FechaComprobante",

  "FechaPrimerVencimiento",

  "CondiciondePago",

  "CUIT",

  "NroComprobante",

  "UnidaddeNegocio",

  "Total",

  "UsuariaLinkeada"

FROM "SISPER"."dbo"."BI_Comprobantesx3";

left join (BI_Comprobantesx3)

LOAD ID,

    Sum(ImporteCobrado) as PaymentAmount;

SQL SELECT ID,

    ImporteCobrado

FROM SISPER.dbo."BI_Recibos_VencimientosCobrados"

group BY ID;

[harishicon]

Joint the both table using left join and in the end use group by ID

[swuehl]

Your GROUP BY is applied to the SQL part of your code, so you would also need to do the aggregation within the SQL.