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

[swuehl]

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

[jagan]

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.

[#4]

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?

[#5]

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

[swuehl]

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?

[#7]

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

[rubenmarin]

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;

[swuehl]

See also

NULL handling in QlikView

[PradeepReddy]

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&'|'))