Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

how to join many payments related to the same bill

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

1 Solution

Accepted Solutions
swuehl
MVP
MVP

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;

View solution in original post

6 Replies
sunny_talwar

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

Not applicable
Author

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

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;

Not applicable
Author

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

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

swuehl
MVP
MVP

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