View Filtered by Current Date

Question

I know it’s possible to filter a view based on a static date. Is it possible to filter a view so the date is “today’s date” rather than a predefined date?

I have a custom asset for data set certifications that includes an expiration date. I would like to have two views- one that shows those that have not expired based on expiration_date > current_date and one that shows those that have expired.

Mehjabeen Sultana · Answer

I referred to this post https://datacitizens.collibra.com/forum/t/refresh-conflict-notifications/151/15 I don’t want to delete the refresh conflict information.

arthurburkhardt · Answer

Well, if you can identify a relatively small number of assets (by typeId or domainId or communityId), it can be relatively easy to loop through them.
If you have complex query needs, I would recommend to use the outputModuleApi. Here’s such an example that can help filter out those assets that have a “due date” attribute greater than a certain threshold. You can use the output module via java and rest api.
The output module allows you to go crazy and filter on status, community, domain, asset type, attributes, and more, in a very efficient way.

Here’s a small python example

last_month = datetime.datetime.today() - relativedelta.relativedelta(months=1)
threshold = int(last_month.timestamp())*1000

query = {"TableViewConfig": {
    "Resources": {
		"Term": {
			"Id": {"name": "id"},
			"Signifier": {"name": "name"},
			"ConceptType": {"Id": {"name": "assetTypeId"}, "Name": {"name": "assetType"}},
			"Vocabulary": {"Id": {"name": "domainId"},  "Community": {"Id": {"name": "communityId"}}},
			"Status": {"Signifier": {"name": "status"}},
			"DateAttribute": [{"labelId": "c0e00000-0000-0000-0001-000500000193", "Date": {"name": "dueDate"}},],
			"Filter": {"AND": [
                        {"OR": [{"Field": {"name": "assetTypeId", "operator": "EQUALS", "value": "daaf28e9-a73b-4d83-bd7f-db110512c632"}},
                                {"Field": {"name": "assetTypeId", "operator": "EQUALS", "value": "548585f7-ac1b-4483-8ced-08d570c0d9b0"}}]},
                        {"Field": {"name": "communityId", "operator": "EQUALS", "value": "4f4670ba-eaf9-428d-97f7-d350b879b668"}},
                        {"Field": {"name": "dueDate", "operator": "GREATER", "value": threshold}},
            ]}
		}
	},
	"Columns": [
		{"Column": {"fieldName": "id"}},
        {"Column": {"fieldName": "dueDate"}},
		{"Column": {"fieldName": "status"}},
		{"Column": {"fieldName": "Due date"}},
	]
}}
r = c.post('outputModule/export/json', json=query)

df = pd.json_normalize(r.json()['aaData'])
df['Due Date'] = df.dueDate.apply(lambda x: datetime.datetime.fromtimestamp(int(x)/1000).isoformat() if x else '')
df

	id	dueDate	status	Due Date
0	121b5eb6-e098-4a8e-9e82-895d80f550ba	1632960000000	Resolved	2021-09-30T02:00:00
1	2a72390e-472b-4d36-a97e-df87b49e6f52	1628208000000	Declined	2021-08-06T02:00:00
2	2e3a9d08-c40b-44d0-8094-36ca5356120e	1629417600000	Accepted	2021-08-20T02:00:00
3	3e146a2e-12ca-4a11-aaec-a71e26aed719	1629417600000	Accepted	2021-08-20T02:00:00
4	46945ca6-c703-41ad-837f-13b9863d5dec	1630454400000	Accepted	2021-09-01T02:00:00
5	4c4d0a65-111f-4081-af23-9aef95e168bd	1629244800000	Accepted	2021-08-18T02:00:00

arthurburkhardt · Answer

Well, if you can identify a relatively small number of assets (by typeId or domainId or communityId), it can be relatively easy to loop through them.
If you have complex query needs, I would recommend to use the outputModuleApi. Here’s such an example that can help filter out those assets that have a “due date” attribute greater than a certain threshold. You can use the output module via java and rest api.
The output module allows you to go crazy and filter on status, community, domain, asset type, attributes, and more, in a very efficient way.

Here’s a small python example

last_month = datetime.datetime.today() - relativedelta.relativedelta(months=1)
threshold = int(last_month.timestamp())*1000

query = {"TableViewConfig": {
    "Resources": {
		"Term": {
			"Id": {"name": "id"},
			"Signifier": {"name": "name"},
			"ConceptType": {"Id": {"name": "assetTypeId"}, "Name": {"name": "assetType"}},
			"Vocabulary": {"Id": {"name": "domainId"},  "Community": {"Id": {"name": "communityId"}}},
			"Status": {"Signifier": {"name": "status"}},
			"DateAttribute": [{"labelId": "c0e00000-0000-0000-0001-000500000193", "Date": {"name": "dueDate"}},],
			"Filter": {"AND": [
                        {"OR": [{"Field": {"name": "assetTypeId", "operator": "EQUALS", "value": "daaf28e9-a73b-4d83-bd7f-db110512c632"}},
                                {"Field": {"name": "assetTypeId", "operator": "EQUALS", "value": "548585f7-ac1b-4483-8ced-08d570c0d9b0"}}]},
                        {"Field": {"name": "communityId", "operator": "EQUALS", "value": "4f4670ba-eaf9-428d-97f7-d350b879b668"}},
                        {"Field": {"name": "dueDate", "operator": "GREATER", "value": threshold}},
            ]}
		}
	},
	"Columns": [
		{"Column": {"fieldName": "id"}},
        {"Column": {"fieldName": "dueDate"}},
		{"Column": {"fieldName": "status"}},
		{"Column": {"fieldName": "Due date"}},
	]
}}
r = c.post('outputModule/export/json', json=query)

df = pd.json_normalize(r.json()['aaData'])
df['Due Date'] = df.dueDate.apply(lambda x: datetime.datetime.fromtimestamp(int(x)/1000).isoformat() if x else '')
df

	id	dueDate	status	Due Date
0	121b5eb6-e098-4a8e-9e82-895d80f550ba	1632960000000	Resolved	2021-09-30T02:00:00
1	2a72390e-472b-4d36-a97e-df87b49e6f52	1628208000000	Declined	2021-08-06T02:00:00
2	2e3a9d08-c40b-44d0-8094-36ca5356120e	1629417600000	Accepted	2021-08-20T02:00:00
3	3e146a2e-12ca-4a11-aaec-a71e26aed719	1629417600000	Accepted	2021-08-20T02:00:00
4	46945ca6-c703-41ad-837f-13b9863d5dec	1630454400000	Accepted	2021-09-01T02:00:00
5	4c4d0a65-111f-4081-af23-9aef95e168bd	1629244800000	Accepted	2021-08-18T02:00:00

Former User · Answer

Thanks, Arthur. My thought was something similar to that, but I don’t know how to search search through multiple assets to find those that match the criteria.

arthurburkhardt · Answer

How about this solution?
Create one attribute called “Up for revision” or “Due today”
Create a workflow that runs on a daily basis:

find all the assets that match the criteria (e.g. due date = today or last update = 365 years ago), and add the attribute to them
find all the assets that do not meet the criteria anymore, and remove the attribute from them.

This way, you can create a view that will only show assets that match the criteria for a given day.

If you want an example workflow that uses a daily time trigger: Daily Notification of Refresh Conflicts - Collibra Marketplace

S Benson · Answer

I have a similar, but slightly different question. Can a view be created to find an Asset with a date 1 year past? But of course, the date will be dynamic. For instance, find every Agrement with a “Last Reviewed Date” of today-365?

Scott

Former User · Answer

jeff.greene:

A custom worflow or API script would have to be created to search the assets and compare the current date to the experation date and then create the views.

Thanks for the suggestion, but how do I cycle/search through all of the assets of a given asset type? Armed with that knowledge, I could do this.

Richard

alvinuseree · Answer

Sadly this isn’t possible yet. If your date is always in past, your best bet would be to sort the results by date. Perhaps that is somewhat of a happy compromise?

Former User · Answer

In other words, have the indicated filter always just show the current date rather than setting it manually each time the view is accessed.

Former User · Answer

I store the date in an attribute. I just want to filter by a > or < comparison with that attribute. It sounds like it’s not possible.

noor · Answer

I also would like to know if that is possible. I guess for such a thing, an attribute is required to store the date?

jeffgreene · Answer

Hi Richard,

As of version 7.04.21 there is not an out of the box feature of search search filters that would make the requests possible and make them dynamic. However there are some features / options in the advanced filter that may get close to the solution (close in that it is dynamic). You could filter in advanced filter, last modified in the last x days, or last reviewed in the last x days.

A custom worflow or API script would have to be created to search the assets and compare the current date to the experation date and then create the views.

Best,
Jeff

1	Grant Rollerson SUNCORP CORPORATE SERVICES PTY LTD
2	Tom Friesen Algonquin College
3	SB S Benson Plus3 IT
4	S stefan_busch
5	Brian Lloyd USA TODAY Co.
6	D daverichard