topic How to match positive and negative values in QlikView

How to match positive and negative values

Thu, 26 Jan 2012 14:06:09 GMT

Hi all,

I have a list of positive and negative values like below:

-1000

-900

-800

-700

-600

-500

200

300

400

500

600

800

1000

I am trying to create something able to eventually match the values with same amount (and different sign: -1000,1000) showing the negative ones that doesn't have a positive match....

Is it possible to do something like this?

Thanks

francesco

Re: How to match positive and negative values

matt_crowther — Thu, 26 Jan 2012 15:39:54 GMT

Francesco,

Bit of a work a round but assuming you have no more than one instance of a positive value and one of a negative value then in the load script place a fabs() function around the field to make all the values positive, then in the application create a list box to containing the field and select 'Show Frequency' under the General Properties tab; all those with a frequency of '1' mustn't have a matching number.

If you have multiple identical positive / negative values it's a little trickier, the oad script may look something like the below:

Data:

Load Value

From Values.xls

(biff, embedded labels, table is Sheet1$);;

Negative_Values:

Load Value as Negative_Value

fabs(Value) as Value

Resident Data Where Value <0;

Positive_Values:

Load Value as Positive_Value,

Value

Resident Data Where Value>=0;

Drop Table Data;

You should then have 2 tables joined on 'Value'. In the app display the fields 'Positive_Values' and 'Negative_Values' in Listboxes, select all values within the 'Negative_Values' Listbox then right-click on the 'Positive_Values' Listbox and 'Select Excluded' - these will be the positive values from your data without a matching negative value.

Hope that cracks it.

Matt - Visual Analytics Ltd

New Qlikview Design Blog: http://QVDesign.wordpress.com

How to match positive and negative values

Thu, 26 Jan 2012 15:42:00 GMT

Hi,

You can use this too

Separate negative values, and use the function Sign and EXISTS:

Table_A:

LOAD F1 AS Value INLINE [

-500

-400

-300

-200

-100

100

300

400

500

];

Table_Negative_Value:

LOAD

Value AS ValueNegative,

IF(SIGN(Value) = -1, Value * (-1), Value) AS UnSignValue

RESIDENT Table_A

WHERE Sign(Value) = -1;

Result:

NOCONCATENATE

LOAD *

RESIDENT Table_Negative_Value

WHERE NOT EXISTS(Value, UnSignValue);

drop table Table_Negative_Value;

drop table Table_A;

How to match positive and negative values

swuehl — Thu, 26 Jan 2012 15:49:04 GMT

Another idea:

Just create an additional ABS field with absolute number:

VALUES:

LOAD *, fabs(Values) as ABS INLINE [

Values

-1000

-900

-800

-700

-600

-500

200

300

400

500

600

800

1000

];

Then create a listbox with a field expression:

=aggr(if(Values<0 and count(distinct Values) =1,Values),ABS)

To list the negative Values without matching positive counterpart.

Hope this helps,

Stefan

How to match positive and negative values

Mon, 30 Jan 2012 08:12:02 GMT

Hi,

Thanks for the suggestions, it is better to go deeper on the problem description...

I have this list of positive and negative values coming from a major list of transactions where the positive values are revenues and the negative ones are returned goods or credit notes...there are thousands of values coming from filtering a major table data. What I'm looking for is to create a check to match each single negative with it's related positive transaction in order to identify the suspicious negative values...

I cannot load singularly those values because they are changing depending on previous filtering (i.e. reseller) and it is possible to have same values for different transactions.....

I hope this coul help....

Thanks

Francesco

Re: How to match positive and negative values

orital81 — Mon, 30 Jan 2012 08:36:08 GMT

Hi, Check the attached file

When Count(Fabs(Values))>1 then you have a matching value

How to match positive and negative values

Mon, 30 Jan 2012 08:54:39 GMT

Hi, unfortunately I cannot open other's documents...could you describe it?

Sorry

How to match positive and negative values

swuehl — Mon, 30 Jan 2012 10:34:55 GMT

Maybe I have misunderstood your requirements, but I still think my solution should fit your needs (i.e. what you described in your second last post), being selection sensitive. If not, could you update your sample data (e.g. with an additional field) to match better your real situation?

If you need to filter out transaction ID or customers instead of tha values, I would suggest that you check for balanced accounts, i.e. maybe check for customers where the sum of transactions is still negative, or something like this.

Re: How to match positive and negative values

Mon, 30 Jan 2012 11:06:38 GMT

TEMP1:

LOAD * INLINE [

Test

-1000

-900

-800

-700

-600

-500

200

300

400

500

600

800

1000

];

Positive:

NoConcatenate LOAD

Test AS Positive

RESIDENT TEMP1

WHERE Test > 0

;

Negative:

NoConcatenate LOAD

FABS(Test) AS Negative

RESIDENT TEMP1

WHERE Test < 0

;

DROP TABLE TEMP1;

NoMatch_Positive:

LOAD

Positive AS Number

RESIDENT Positive

WHERE NOT EXISTS(Negative, Positive)

;

NoMatch_Negative:

NOCONCATENATE LOAD

Negative AS Number

RESIDENT Negative

WHERE NOT EXISTS(Positive, Negative)

;

DROP TABLES Negative, Positive;

NoConcatenate

NoMatch:

LOAD

RESIDENT NoMatch_Negative

;

CONCATENATE (NoMatch)

LOAD

RESIDENT NoMatch_Positive

;

DROP TABLES NoMatch_Negative, NoMatch_Positive;

This one is for both (positive and negative).

If you want only the negatives, you can skip the last part and only match one table with the other.

Kind Regards,

Reyman

How to match positive and negative values

vijay_iitkgp — Mon, 30 Jan 2012 11:21:51 GMT

Hi,

If your negative numbers are returns or credit notes then it must be associated with positive numbers on some key like invoice number and date .

You can do like:

Table1:

Load

A,B,C,Key,

Amount as Revenue

From ABC.qvd Where Amount>=0

Join

Load

Key,

Amount as Return

From ABC.qvd Where Amount<0;

Table2:

Load

If(Revenue=Fabs(Return),'OK','Problem') as Flag

Resident Table1;

Drop table1;

Hope this will help.