Skip to Content
0
Former Member
Oct 05, 2016 at 09:05 PM

Compare Variable in Query 1 with Query 2 and get those where no result exists in Q2

21 Views

Hi BO community

I have two queries in my report:

Universe Query1 (UQ1):

Type Location Company Cost Center Amount T1 City1 COA CC1 100 T2 City2 COA CC2 200 T2 City2 COA CC3 50

T2

City1 COB CC1 150 T2 City2 COB CC2 100

Universe Query2 (UQ2):

Company Cost Center COA CC1 COA CC2 COA CC3 COB CC2 COC CC1 COC CC3

In the webi report I have a formula [Ref Company]:

=If [UQ1].Type = "T2" and [UQ1].[Location] = "City2" and [UQ1].[Company] InList ("COA";"COB") then "COC" Else [UQ1].[Company]

Which results the following table

Ref Company Type Location Company Cost Center Amount COA T1 City1 COA CC1 100 COC T2 City2 COA CC2 200 COC T2 City2 COA CC3 50 COB

T2

City1 COB CC1 150 COC T2 City2 COB CC2 100

Now based on UQ2 I would like to check if certain [Ref Company] and [Cost Center] combination exists like:

Ref Company Cost Center RefCo Cc Combo Exists Type Location Company Amount COA CC1 Yes T1 City1 COA 100 COC CC2 No T2 City2 COA 200 COC CC3 Yes T2 City2 COA 50 COB CC1 No

T2

City1 COB 150 COC CC2 No T2 City2 COB 100

Is there any way to do get [RefCo Cc Combo Exists] with a formula?

What I tried is:

= [UQ2].[Company] Where ([UQ2].[Company] in [Ref Company])

but it results empty values for all COC / CC combinations.

Please help, thanks in advance!