Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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;
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
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
Hi, Check the attached file
When Count(Fabs(Values))>1 then you have a matching value
Hi, unfortunately I cannot open other's documents...could you describe it?
Sorry
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.
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
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.