MINUS in QlikView

[#1]

Hi,

Could some one help us in extracting the same output as that provided by MINUS keyword in Oracle.

I have elaborated the issue with an example below:

Main Query o/p:

Year	Division	D_ENTRY_DATE	Currency	Type	Amount
04	A1	02/05/2007	USD	R	-40338.4
04	A2	02/05/2007	USD	C	271453
04	A3	23/05/2007	USD	C	55.73
06	A3	18/09/2008	USD	R	-793.36
06	A3	13/05/2009	USD	R	810.39
04	A3	30/11/2009	USD	C	345.14
04	A3	30/11/2009	USD	R	-52.15
05	A3	01/07/2010	USD	T	1.6
04	A1	26/03/2010	USD	T	0.4
05	A2	26/03/2010	USD	T	1.6

Minus Query o/p:

Year	Division	D_ENTRY_DATE	Currency	Type	Amount

No rows returned based on the below conditions(there are no records with Type as 'P'):

Where Type = 'P' And Division in ('A1', 'A2') And Year In ('05','06')

To get the similar output in QlikView, we have used the conditions of Minus Query as - (Exclusion) in Set Analysis.

sum({$<[Type] -= {'P'}, [Policy Year] -= {'05', '06'}, [Division Code]-={'A1', 'A2'}>} Amount)

The sum results in zero for the last 3 rows above.

Year	Division	D_ENTRY_DATE	Currency	Type	Amount
05	A3	01/07/2010	USD	T	0
04	A1	26/03/2010	USD	T	0
05	A2	26/03/2010	USD	T	0

Please advise if the conditions in the set analysis have been included correctly. Also, we need to exclude the rows that match with the above conditions.

Regards,
Rajendra

[sbaldwin · Accepted solution]

Hi i am not sure your set algebra is quite right as by adding you minus querys to the same set modifyer you are essentially saying AND, but you are modifying it with not conditions, have you tried something like:

({$- $<[Type] = {'P'}, [Policy Year] = {'05', '06'}, [Division Code]={'A1', 'A2'}>}

so you are taking the entire "Minus" set and taking it from the current selection?

(that is if i understand you correctly!!! )

Thanks

Steve

[#3]

Thanks Steve,

This helped in the exclusion of the rows. However we are unable to find how to apply OR operator in set analysis.

Could you please help us in applying OR operator in Set analysis.

Example in SQL:

WHERE Group = 'PR' AND DivisionCode IN ('A1', 'A2') AND (Type  !=  'Batch PNP'
OR
(Type  =  'Batch PNP'
AND 
Year IN ('05', '06')))

This when converted in Set analysis as a negate would be:

({$- $<[Group] = {'P'}, [DivisionCode]={'A1', 'A2'},[Year] = {'05', '06'},[Type] = {'Batch PNP'}>}

How to apply Type  !=  'Batch PNP' and the OR condition?

Could you please advise.

[sbaldwin]

Hi in set analysis this is a union so you would need to do something like this:

({    ($- $<[Group] = {'P'}, [DivisionCode]={'A1', 'A2'},[Year] = {'05', '06'},[Type] = {'Batch PNP'}>)+$<Type  -= { 'Batch PNP'>   }

Thanks

Steve

[#5]

Thanks Steve,

Almost there, but looks like I am missing something.

It is mentioned that

A AND (B OR C)

Becomes:

(A AND B) OR (A AND C)

Which becomes something like:

{$<A, B> + {<A, C>}

When I apply the filter as advised by you, it is displaying data only based on the 1st filter.

Formula 1:

sum({(1- $<[V_HOW_MADE_PAYABLE_SDESC] -= {'Batch PNP'}>)+$<[V_TOE_GROUP] = {'PR'}, [V_POLICY_YEAR]={'05', '06', '07', '08', '09', '10'},

[V_DIVISION_CODE] = {'E', 'F','O'},

[V_TOE_MCD] = {'08', '10', '12', '14', '16'},

[V_HOW_MADE_PAYABLE_SDESC] = {'Batch PNP'}>} AMOUNT)

Shows data only matching with

'Batch PNP'

Formula 2:

sum({($- $<[V_TOE_GROUP] = {'PR'}, [V_POLICY_YEAR]={'05', '06', '07', '08', '09', '10'},

[V_DIVISION_CODE] = {'E', 'F', 'O'},

[V_TOE_MCD] = {'08', '10', '12', '14', '16'},

[V_HOW_MADE_PAYABLE_SDESC] = {'Batch PNP'}>)+$<[V_HOW_MADE_PAYABLE_SDESC] -= {'Batch PNP'}>} AMOUNT)

Does not filter out the rows with 'Batch PNP'

Could you please advise the correct syntax that needs to be followed.

[#6]

Steve,

Just wanted to updated that the syntax to be followed equilvent to the above SQL query is:

Sum ({1-($<[Type] -= {'Batch PNP'}> + $<[Group] = {'PR'}, [Year]={'05', '06'},

[Division Code] = {'A1', 'A2'},

[Type] = {'Batch PNP'}>)} Amount)

Thanks for you help on this.