topic Re: Question in QlikView

Question

Wed, 18 Dec 2013 09:09:13 GMT

Hello, I have this situation:

CustomerGroup	ToBeCashed
A1	2000

ToBeCashed formula: Sum(Credit) - Sum(Debit)

I have these movements:

Customer	CustomerGroup	Credit	Debit
PLUTO	A1	2000	0
PIPPO	A1	100	0
PIPPO	A1	0	100

If I add a listbox with only the customer field I have the following situation:

Customer
PLUTO
PIPPO

Now the question is: How can I have in the list box only the presence of the PLUTO customer, I don't want the customers where the difference between credit and debit is zero.

Thanks.

Re: Question

swuehl — Wed, 18 Dec 2013 09:28:23 GMT

Try maybe something like

=aggr( if(sum(Credit)<>sum(Debit), Customer), Customer)

Re: Question

sujeetsingh — Wed, 18 Dec 2013 09:46:06 GMT

Swuel is right see the sample

Re: Question

IAMDV — Wed, 18 Dec 2013 10:15:15 GMT

Hi,

You need to solve this piece in the script instead of UI. I'm sharing part of the script and you can use the below script to integrate in your application.

//This code is incremental reload where we are filtering Customers where DR & CR amount are not equal

NoConcatenate

LOAD

Customer ,

New_Customer

where CR_Amount <> DR_Amount;

//This code is resident load where we are grouping DR & CR amount

Final:

LOAD Customer , Customer as New_Customer, Sum(Credit) AS CR_Amount, Sum(Debit) AS DR_Amount

Resident Test

Group by Customer;

If this is not clear... please share full script or sample application.

I hope this helps!

Cheers,

Re: Question

Wed, 18 Dec 2013 11:14:08 GMT

Thank you, I followed your suggestion slightly modified and it works.

=aggr( if(sum(Credit)<>sum(Debit), Customer, ''), Customer)

Many thank to everyone!!