Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
Not applicable

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

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

Tags (2)
1 Solution

Accepted Solutions
Partner
Partner

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

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

View solution in original post

20 Replies
MVP
MVP

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

If the records exist, only field PaymentDate show NULL, you can try

LOAD
IKey,
"Payments_ID",
"AccountDetail_ID",
If(Len(Trim(PaymentDate)),PaymentDate,'No Payment Date') as PaymentDate,
PaymentAmount,
PrositStatus;
SQL SELECT *
FROM "Test".dbo.Invoice

MVP & Luminary
MVP & Luminary

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

Hi,

LOAD
IKey,
"Payments_ID",
"AccountDetail_ID",
PaymentDate,

If(Len(Trim(PaymentDate)) = 0, 'No Payment Date', 'With Payment Date') AS PaymentDateFlag,
PaymentAmount,
PrositStatus;
SQL SELECT *
FROM "Test".dbo.Invoice;


Now using set analysis you can filter easily,for example


Chart : Straight Table

Dimension: Payment_ID

Expression: Sum({<PaymentDateFlag={'No Payment Date'}>}PaymentAmount)


Hope this helps you.


Regards,

Jagan.

Not applicable

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

Hi i tried your solution but the inly value i get to filter 'With Payment Date'. So there are no records without PaymentDate and thats my problem. The PaymentDate does not exist Before the payment i done. I need somehow put a value to all paymentsdates that does not exist.

i tried this

SET NullValue='Unknown';

NULLASVALUE PaymentDate;

and this dosent work. Any ideas?

Not applicable

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

Hi thx, but the paymentdate does not exist Before the payment has been done, therefore the value dosent show as null.

MVP
MVP

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

As I said, the records need to exist before you can change a field's NULL to a value.

Are you talking about missing values, not NULLs?

Can you post some sample lines of data?

Not applicable

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

Yes its missing values not null,

Invoice referens ,PaymentDate

sample data when loadin in Qlikview and showin it in a straight table

referens PaymentDate

343453    2012-04-21

343435     -

342433    2012-04-21

But when i look in the Database the value 343435 PaymentDate does not exist

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

Hi Theodor, I don't know how you want to show invoices, in axample, in a text box separated by commas this expression can work:

=Concat({<key=E({<PayMentDate={"*"}>})>} Number, ',')

I tested with this script:

Invoice:

load * inline

[

key, Number

1,1

2,2

3,3

];

Payments:

load * inline

[

key, PayMentDate

1,01/01/2015

];

// Not using NULLASVALUE;

MVP
MVP

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

pradeepreddy
Valued Contributor II

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

Try, by doing Left join

Load *

from Invoices table

where invoice_amt>1;

Left join

Load *

from Invoice_Payment Table;

Edited:

=Concat(if(isnull(Payment_DT),InvoiceNo&'|'))