topic Re: MINUS in QlikView in QlikView

MINUS in QlikView

Tue, 18 Oct 2011 07:25:17 GMT

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

Re: MINUS in QlikView

sbaldwin — Tue, 18 Oct 2011 11:27:55 GMT

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

Re: MINUS in QlikView

Thu, 20 Oct 2011 10:57:23 GMT

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.

MINUS in QlikView

sbaldwin — Thu, 20 Oct 2011 11:05:23 GMT

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

Re: MINUS in QlikView

Thu, 20 Oct 2011 13:20:27 GMT

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.

Re: MINUS in QlikView

Fri, 21 Oct 2011 06:41:35 GMT

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.