topic Re: Deleting Cleared Payments in QlikView

Deleting Cleared Payments

Tue, 17 Dec 2013 19:23:54 GMT

Hi all,

I am trying to figure out a code that would change the Invoice Amount from the original amount to 0 if the payment has cleared. However, every time I enter my code, the program stops and crashes. Is there something wrong with my code/anything that could make it better? I've listed it below:

Left Join

Load [Clearing Date],

[Invoice Amount (Trading Currency)],

if([Clearing Date]>0,0,[Invoice Amount (Trading Currency)]) as [Amount Due]

Resident [XX];

//DROP Table [XX];

Thank you,

Re: Deleting Cleared Payments

Nicole-Smith — Tue, 17 Dec 2013 20:19:25 GMT

You need to name the table that you're left joining to:

Left Join (YourTable)

Load [Clearing Date],

[Invoice Amount (Trading Currency)],

if([Clearing Date]>0,0,[Invoice Amount (Trading Currency)]) as [Amount Due]

Resident [XX];

//DROP Table [XX];

Re: Deleting Cleared Payments

Gysbert_Wassenaar — Tue, 17 Dec 2013 20:24:45 GMT

What table are you joining on? It looks like you might create a lot of bogus records, possibly even a cartesian product.

I would expect that you would at least need a payment id in both tables to make sure you join records of the same payment.

Re: Deleting Cleared Payments

IAMDV — Wed, 18 Dec 2013 09:47:11 GMT

Hi John,

Gysbert made logical point! What is the Join Predicate whilst you're using Left Join? Also, it's good practice to mention the table name as the parameter after "Left join" statement. It will be easy if you can attach the sample application or at least full script.

Did you try to join without creating the new calculated field?

Cheers,