Filtering Based on another Filter
Altair EmployeeI am using Monarch 11.9
Is it possible to use the output from one filter in another filter?
I have a series of data similar to
1000 21 1 A
1000 22 1 A
1000 23 1 A
1000 90 1 A
1001 21 1 A
1001 22 1 A
1001 23 1 A
1005 21 1 A
1005 22 1 A
1006 23 1 A
My first filter grabs anything with a 21 on the line.
The second filter grabs everything that doesn't have a 21 on the line.
However what I want to do is if the number in the sequence appears on the first filter don't put it on the second filter.
Or compare the output from filter 2 to filter 1 and only show items that appear on filter 2 that are not on filter 1 based on the first numbers, ie 1000
Answers
-
Hi Philip,
I think you can do this using the negative join (or an external lookup in Monarch Classic)
If I'm understanding the problem:
Data set 1 will be
1000 21 1 A
1001 21 1 A
1005 21 1 A
data set 2 will be
1000 22 1 A
1000 23 1 A
1000 90 1 A
1001 22 1 A
1001 23 1 A
1005 22 1 A
1006 23 1 A
And you want the result to be:
1006 23 1 A
Joining the two sets on the first column will allow you to compare sets. The negative join in Data Prep Studio will be by far the neatest solution. If you have Monarch Classic, then you would need to export the first filter, then join it back in on a 2nd pass using the external lookup.
Please let me know what version you have if you'd like a more detailed answer.
EDIT - My apologies. I've just seen you have specified Monarch 11.9. I'm afraid that forces the export and 2nd pass route. Let me know if you need more info.
Regards.
Steve.
0 -
Altair Forum User said:
Hi Philip,
I think you can do this using the negative join (or an external lookup in Monarch Classic)
If I'm understanding the problem:
Data set 1 will be
1000 21 1 A
1001 21 1 A
1005 21 1 A
data set 2 will be
1000 22 1 A
1000 23 1 A
1000 90 1 A
1001 22 1 A
1001 23 1 A
1005 22 1 A
1006 23 1 A
And you want the result to be:
1006 23 1 A
Joining the two sets on the first column will allow you to compare sets. The negative join in Data Prep Studio will be by far the neatest solution. If you have Monarch Classic, then you would need to export the first filter, then join it back in on a 2nd pass using the external lookup.
Please let me know what version you have if you'd like a more detailed answer.
EDIT - My apologies. I've just seen you have specified Monarch 11.9. I'm afraid that forces the export and 2nd pass route. Let me know if you need more info.
Regards.
Steve.
Model 1 will just have the first filter in it, which would be exported to a csv for example. Then close that model down.
Model 2 will just have the 2nd filter in it and an external lookup to the file you created from model 1. Join it just on the column with the 1000 etc in it and import the column with 21 etc in it.
This should result in
1000 22 1 A 21
1000 23 1 A 21
1000 90 1 A 21
1001 22 1 A 21
1001 23 1 A 21
1005 22 1 A 21
1006 23 1 A (null)
(where the last column is the one you joined from the external lookup.)
Refining the 2nd filter using IsNull(FieldName) on the joined field and hiding the joined columns should result in
1006 23 1 A
0 -
Just for clarification:
Might "21 in the line" include
1005 22 1 A
under any possible circumstances?
I would assume not but have learned that it is always a good idea to double check!
Grant
0 -
Altair Forum User said:
Just for clarification:
Might "21 in the line" include
1005 22 1 A
under any possible circumstances?
I would assume not but have learned that it is always a good idea to double check!
Grant
It wouldn't include the 22 1 because I have the model setup to break on the space.
We have been doing the work via excel vlookup I was just looking for a way that would take the human factor out of it.
0 -
Altair Forum User said:
It wouldn't include the 22 1 because I have the model setup to break on the space.
We have been doing the work via excel vlookup I was just looking for a way that would take the human factor out of it.
Philip,
Steve's suggestion for you version of Monarch is the logical and easiest to create and check approach, so it has practical benefits.
In similar situations I have reduced this to one model that is run twice, the first time to deliver Steve's model 1 output (leaving the filter links un-linked of course!) and the second time to to add in the filter created from the first run. Basically the same process that Steve described but contained in a single model.
It worked very well so long as you (and all users) were very clear about what they needed to do and there was a mechanism to ensure that the output files from previous runs were not re-used erroneously. That might be a simple procedural step in the instructions for running the process but better would be to build something in the makes it impossible to accidentally use an "old" filter file or at the very least flags up some warnings if one tries to.
All easily handled but the best approach for your needs is likely to depend on what is in your source files.
That said, in this case it may be as simple as making sure the second filter file produced from the first pass transactions has the same file name and date as the active file.
Does that help?
Grant
0 -
Altair Forum User said:
Philip,
Steve's suggestion for you version of Monarch is the logical and easiest to create and check approach, so it has practical benefits.
In similar situations I have reduced this to one model that is run twice, the first time to deliver Steve's model 1 output (leaving the filter links un-linked of course!) and the second time to to add in the filter created from the first run. Basically the same process that Steve described but contained in a single model.
It worked very well so long as you (and all users) were very clear about what they needed to do and there was a mechanism to ensure that the output files from previous runs were not re-used erroneously. That might be a simple procedural step in the instructions for running the process but better would be to build something in the makes it impossible to accidentally use an "old" filter file or at the very least flags up some warnings if one tries to.
All easily handled but the best approach for your needs is likely to depend on what is in your source files.
That said, in this case it may be as simple as making sure the second filter file produced from the first pass transactions has the same file name and date as the active file.
Does that help?
Grant
Right now I think we are going to stick with the process we are currently using. It's understood and they are comfortable with it so we are going to stay the course.
0