I have a google sheet with columns for Beg Date and End Date for several years (2019 Beg Date, 2020 Beg Date, etc). I have a user parameter for Year. I have a parameterFetcher to get the parameter but then how do I filter for only that year. If the user enters 2020 how do I only utilize 2020 Beg Date and 2020 End Date. Right now all of the years (2019 Beg Date …) show up as attributes.
Question
How do I select only the attribute that contains user parameter.
This post is closed to further activity.
It may be an old question, an answered question, an implemented idea, or a notification-only post.
Please check post dates before relying on any information in a question or answer.
For follow-up or related questions, please post a new question or idea.
If there is a genuine update to be made, please contact us and request that the post is reopened.
It may be an old question, an answered question, an implemented idea, or a notification-only post.
Please check post dates before relying on any information in a question or answer.
For follow-up or related questions, please post a new question or idea.
If there is a genuine update to be made, please contact us and request that the post is reopened.
+1You might be looking for a tester, as simple as it seems.
Just do a double test ( beginning_date =2020 Beg Date and end_date = 2020 End Date).
You just have to change the names so it fits.
If it is not it, could you please explain with more details ?
+12- Contributor
Hi @tsurveyor ,
I don't know if I understood right, but it seems you have a dataset with a number of columns potentially increasing to infinity, and it's not a good practice. Can you please provide an example?
I don't think Tester will work. I did find a work around but was looking for something more dynamic. The dates coming in from the reader:
There is a user parameter for month and year. So if I want to run the report from March 2019, I need to extract the 2019 Beg Date and 2019 End dates for March. I have a tester for the month - that's the easy part. How do I then extract just the 2019 dates? My work around is a TestFilter by year
Then I have have an AttributeManager to remove the years I don't want and change the attribute name I want to just BegDate and EndDate so it's generic going forward.
The selected dates then move on later to merge with my data and be filtered.
General flow
This is a very manual way to do it that means adding to this list as time goes forward. Is there a more dynamic way to get only the dates I need?
+12- Contributor
Hi @tsurveyor ,
Basically the source schema is 'Closing Month' followed by one ore more pairs of '<year>_beg_date', '<year>_end_date'. Let me simplify the attribute names and call them
'closing_month', '<year>_beg', '<year>_end'.
The main idea is to use the names of the attributes '<year>_beg', '<year>_end' to restructure the data and obtain a four attribute schema: 'year', 'closing_month', 'beg', 'end' and then filter the resulting dataset against the input parameters.
We can proceed as follow:
- create a '_fid' unique identifier for each table row (maybe it's not strictly necessary, but can help to clarify the flow) with a Counter;
- explode the attributes with an AttributeExploder, so two new attributes are added '_attr_name' and '_attr_value';
- filter out the unnecessary features (Tester) so to keep only the ones with begin and end dates (e.g. 2021_beg, 2021_end, 2020_beg, 2020_end and so on);
- create three new attributes (AttributeCreator) 'year', 'beg' and 'end';
- finally aggregate by 'year' and '_fid'.
Here is the the workspace
...the output of the AttributeKeeper
...the output of the Aggregator
...and the final output with input month 'January' and year '2020'.
It should work!
@davtorgh Thanks for that extensive explanation. I ended up deciding that the best solution was the simplest. I moved all of the calendar dates so it's one long list. My columns are now Closing_Month, Year, Beg_Date, and End_Date. So a simple test for month and year gets me the right dates. Not sure why I fought doing it this way in the first place since aesthetics are not relevant for this spreadsheet.