Hi,
I have 2 columns with id’s.
I want to make a filter which returns only those values that occure in both columns.
Exemple;
Line ID1 ID2
1 1234567 2345678
2 7654321 7654321
3 3456789 1456789
4 2123242 2123242
5 3213124 3452346
So, in the above exemple it should return only line 2 and line 4
You cannot create a filter directly comparing two objects. However, you can create a variable that compares those two objects and then filter on that. Like this…
=If([ID1]=[ID2]; 1; 0)
And with your formula, Webi knows that it has to compare the Values within these variable?
I say this because i saw a variable somewhere, where they put “Value” in front of the variable.
Yes, the above formula is just comparing the value in attribute [ID1] to the value in attribute [ID2] and returning either a zero or one which you can then filter on.
ok, would be great if this works in my report.
Ill let you know, thanks so far:)
hmm. I must be doing something wrong;
my filter only gives the “1” option which is weird because i can see values that are not in the other column. Could it be that the value has to be text or number?
I have to add, that the columns are imported in separate queries and then merged.
What are you merging on?
i have to merge on the id’s.
Unfortunately also which i want to compare.
The underlying question is that i want to see or anyone who has been registered longer then 2 years ago, also is registered in the first querter of 2021.
To do so i think i first have to make 2 queries, one for each period.
The problem is comparing them after i merged these 2. (i merge them to be able to use some details).
Very odd.
I use the compare formula on the numbers in my example and get no results at all. Looking at the numers i should at least get 3
I used this formula;
=Als([PERIODE langer dan 2 jaar geleden]=[PERIODE 1e kwartaal 2021] ;1;0)
Where “Als” obviously is “IF”
merge the two elements so you get only the matching results
make sure that there are no hidden decimals in the numbers
That doesn’t work in my report.
First columns are the merged id’s and the 2e and 3e are the seperate id’s.
The second value in the merged colmun has a value that can’t exist in the column “1e qrt 2021”…
difficult to understand your problem
there are always different columns and different data and different number-formats in each of your examples
To simulate your situation I have create two free-hand SQL queries with data similar to yours…
–Longer than 2 years ago
SELECT ‘000000000’ AS [ID], ‘05-01-2017’ [SomeDate]
UNION
SELECT ‘000000000’, ‘05-03-2017’
UNION
SELECT ‘123456789’, ‘02-04-2018’
UNION
SELECT ‘111222333’, ‘05-03-2018’
UNION
SELECT ‘444555666’, ‘06-27-2018’
UNION
SELECT ‘987654321’, ‘12-04-2018’
UNION
SELECT ‘999888777’, ‘12-15-2018’;–2021 Q1
SELECT ‘000000000’ AS [ID], ‘01-16-2021’ [SomeDate]
UNION
SELECT ‘000000000’, ‘02-07-2021’
UNION
SELECT ‘000000000’, ‘02-13-2021’
UNION
SELECT ‘123456789’, ‘02-24-2021’
UNION
SELECT ‘111222444’, ‘03-03-2021’
UNION
SELECT ‘987654321’, ‘03-19-2021’;
Those queries yield these tables…
Next I merged on the ID objects from each query. Here is where things get a little more difficult.
I created two variables; one for the SomeDate object from each query. I called them Var SomeDate 2021 Q1 and Var SomeDate Two Years Ago. The key is that the Qualification must be “Detail” and the Associated dimension must be the merged ID dimension. Not the ID from either query, but the merged ID dimension that looks like a parent to the ID dimension from each of the queries.
Now we can build a table with the merged ID dimension and the two variables we just created.
The final step is to add filters where both the variables are not null.
And there you have it…
I do not know how to deal with the #MULTIVALUE (#MEERWAARDEN) in this situation, but that is not essential to this particular question.
A couple of other notes. You do not need the variables in the table in order to filter on them. Or you could leave them in the table, but hide them if you do not want to see them.
Also, with this foundation you can show the IDs that are in one query an not the other by adjusting the filters giving these results…
Hope this helps
Hi,
a very late respons, as a result of all the extra work from Covid related issues…
Many thanks, it worked great!
Extra thanks for the effort you put into this, realy appreciate it:)
greetings from the Netherlands,
Erik Murk
