Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

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

Tags (1)
1 Solution

Accepted Solutions
MVP
MVP

Re: how to join many payments related to the same bill

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;

6 Replies

Re: how to join many payments related to the same bill

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

Not applicable

Re: how to join many payments related to the same bill

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

MVP
MVP

Re: how to join many payments related to the same bill

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

Re: how to join many payments related to the same bill

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
New Contributor III

Re: how to join many payments related to the same bill

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

MVP
MVP

Re: how to join many payments related to the same bill

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

Community Browser