Pull value from another query in based on 2 variables

building reports desktopintelligence
1

So I have a report with 2 queries.

Now from the second query I want to lets say field X.
Both queries are merged on the field Material.
Also both have Plant.

So I was thinking something like.

=X where([Plant1]=[Plant2]) and where([Material1]=[Material2])

But I am getting the bad operand in ‘And’ expression error.

rpinxt (BOB member since 2019-01-23)

2

Try this instead:

=X where([Plant1]=[Plant2] and [Material1]=[Material2])

If you’re merging, things should just work though.

Note that you may get errors when trying to use dimensions that are only in one of the queries.

3

Thanks Mark,

However I now am getting #datasync error.

I understand what you are saying that if everything is merged there should not be a problem.
However the problem is a bit more complicated and I tried with a simple question first :wink:

But this is now the code :

=[Query 3].[Special Procurement Type] Where([Query 3].[Plant]=[shPlant] And [Query 3].[Material Display]=[Query 1].[Material Display])

So there are actually 3 queries.
And plant is not that straight forward.

the plant from q1 had 2 characters in front so I made a variable that took last 4 characters and then it is the same as plant from q3.

And Special Procurement Type is only in q3.
So based on the material and plant from q1 (that match with the material and plant from q3) I want to get the Special Procurement Type.

As a sort of excel vlookup.

rpinxt (BOB member since 2019-01-23)