How to match positive and negative values

[#1]

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

[matt_crowther]

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

[#3]

Hi,

You can use this too

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

Table_A:

LOAD F1 AS Value INLINE [

    F1

    -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;

[swuehl]

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

[#5]

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

[orital81]

Hi, Check the attached file

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

[#7]

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

Sorry

[swuehl]

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.

[#9]

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

[vijay_iitkgp]

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.