I want to display all invoices that doesn't have a PaymentDate

[#1]

Hi

I have to tables

One named invoice that displays invoice information. I want to display all invoices that doesn't have a PaymentDate and I cant get it right. I tried NULLASVALUE  any tips? Because PaymentDate only exist after payment the value dose therefore not exist, so how can I find it, and null as value does not seem to work

LOAD
Pid,
IKey,
"Place_ID"
IDate
IAmount,
Status;
SQL SELECT *
FROM "Test".dbo.Invoice
WHERE InvoiceAmount >=1;

LOAD
IKey,
"Payments_ID",
"AccountDetail_ID",
PaymentDate,
PaymentAmount,
PrositStatus;
SQL SELECT *
FROM "Test".dbo.Invoice

thx Theo

[Peter_Cammaert]

There is something weird about your OP: both internal tables are loaded from the same SQL Table "Test".dbo.Invoice, which means that every Invoice comes with a key value and a Payment date which can be NULL but must exist. Also, in the second LOAD you will get more records than in the first because of the additional filter on the first LOAD.

In short: you have more records in your Payments table than Invoice records, but many of the Payment records will contain no data at all. I'm missing something here, as you keep saying that there are no records for unpaid invoices...

[jafari_ervin]

Hi,

Please send your sample document.

best

[Mark_Little · Accepted solution]

HI,

Why don't you Try the below

TEMP:

LOAD

Pid,

IKey,

"Place_ID",

IDate,

IAmount,

Status;

SQL SELECT *

FROM "Test".dbo.Invoice

WHERE InvoiceAmount >=1;

LEFT JOIN(TEMP)

LOAD

IKey,

"Payments_ID",

"AccountDetail_ID",

PaymentDate,

PaymentAmount,

PrositStatus;

SQL SELECT *

FROM "Test".dbo.Invoice;

NoConcatenate

Final:

Load *,

  IF(LEN(TRIM(PaymentDate)) = 0, 1,0) as PaymentFlag

Resident TEMP;

DROP TABLE TEMP;

This Would give you one fact table and also  you have a flag set for where a payment has been made that can then be used in your Set Analysis.

Mark

[#5]

Hi Test is a example acctualy its invoice.dbo and paymends.dbo sorry for the confussion

[#6]

Have you tried to show in a table and sort by PaymentDate?

[Peter_Cammaert]

Then why don't you load your payments first, and while loading your invoices next, check whether payments exist for each invoice. That flag can be used to show Paid/Unpaid invoices with simple set analysis. Like in:

Payments:

LOAD
IKey,

IKey AS PaymnetIKey,

"Payments_ID",
"AccountDetail_ID",
PaymentDate,
PaymentAmount,
PrositStatus;
SQL SELECT *
FROM "Test".dbo.Payments;

Invoices:

LOAD
Pid,
IKey,
"Place_ID"
IDate
IAmount,
Status,

IF (Exists(PaymentIKey, IKey), 'Paid', 'Not Paid') AS PaymentStatus;
SQL SELECT *
FROM "Test".dbo.Invoice
WHERE InvoiceAmount >=1;

DROP Field PaymentIKey;

[#8]

Hi Theodor,

Not sure if tis is what you want, but a slightly different angle might be to create a pivot table with [PAYMENT DATE FIELD] as a dimension and use the following expression;

if(nullcount([PAYMENT DATE FIELD])>0,(count(INVOICE VALUE)))

Fingers crossed this might help?

Regards,

Jason.

[#9]

Thank a lot this fixed the issue. Brilliant!!

[#10]

Thank you for your answer but the problem is fixed now. I didn't have a chance to test all the solutions but the solution from Mark did the trick.

Thank you for your answer

Theo

[#11]

Thank you for your answer but the problem is fixed now. I didn't have a chance to test all the solutions but the solution from Mark did the trick.

Thank you for your answer

Theo