Here are the problems I am trying to solve: If a property has records in the Deed Transfer Records Cleaned table, it is assumed that all property transfer history is captured in this table
*Use document_date as the start_date of a deed transfer transaction. The end_date should be the document_date of the next transaction minus 1 day. If this is the last transaction, then the end_date should be Null.
*The owner of a transaction should be the Grantees
*If a property has no records in the Deed Transfer Records Cleaned table, then use the sale_price in the OPA Properties Cleaned table as the start_date. The owner should be the owner_1 in the OPA Properties Cleaned table. Note that the end_date for these transactions is NULL.
Reference to the sample table that I am trying to accomplish attached.
To start, I was thinking about using the Multi-row Formula function. However, I am not sure what logic algorithm to use to create the end_date column. Please advise on how I should start the process
Solved! Go to Solution.
Hi @jly3292014 !
You're right on track to think of the Multi-Row Formula tool. Try this formula and let us know if it works for you:
datetimeadd([Row+1:document_date],-1,'days')
Don't forget to choose a Group by field. For your use case, I am guessing that you want to group by parcel_number.
@ddiesel thank you very much for your reply. After applying using the Multi-row formula and added the other formula you mentioned, I am stuck on figuring out this statement, "If a property has no records in the Deed Transfer Records Cleaned table, then use the sale_price in the OPA Properties Cleaned table as the start_date. The owner should be the owner_1 in the OPA Properties Cleaned table. Note that the end_date for these transactions is NULL." Please see the snapshot of what I have so far:
Is the statement telling me that if the property (from Property History Dimension table) has no records in the Deed Transfer Records Cleaned table, then set sale_price as start_date? Please advise on how I can resolve the issue.
Hi @jly3292014 !
It would be super helpful if you can upload your workflow.
If I understand you correctly, the properties that have no records in the Deed Transfer Records Cleaned table should be coming out of your Join Tool in the L Anchor. Is that right?
If so, then you can use the Union Tool to combine those parcel_numbers with your J Anchor. The same Multi-Row formula tool from above will work for these records if you rename start_date to document_date right after the L Anchor and before the Union Tool.
@ddiesel I attached the workflow
@jly3292014 Not sure you need to add summarise tool and find the maximum date to set the end date as NULL. The multi-row has the ability to set the last row as NULL for the End Date
Hi again @jly3292014 !
Wow! There’s a lot going on here. Here are a few things to consider:
Take a look at the data structure examples in this workflow and let us know if we are getting closer to your solution.
Thanks,
Deb
@ddiesel @binuacs Thank you both for your help. We are getting closer to the solution.
However, I realized that I forgot to attach the OPA Properties Cleaned table (based on the last sentence of the objective). If I am reading it correctly, shouldn't I join the table with the other two tables that had already joined previously? Please reference the updated attached.
Also, I found out that there's a typo on the objective. Here's the actual objective, "f a property has no records in the Deed Transfer Records Cleaned table, then use the sale_date in the OPA Properties Cleaned table as the start_date. The owner should be the owner_1 in the OPA Properties Cleaned table. Note that the end_date for these transactions is NULL"
Please provide feedback on what issues I need to fix going forward.
@jly3292014 You're right on track again by adding the 3rd file with the join. I think the only piece missing is to rename "owner_1" to "owner" in Select Tool (18).
Try this out and let us know if there's anything else that's not working for you.
@ddiesel @binuacs After reading the problem, I realized that I believe I made a mistake. Here's the actual problem I am trying to solve:
Create a workflow to build a Property History Dimension table. It should meet the following requirements:
After reading the problem above, is it telling me that I need to use only the Deed Transfer Records Cleaned and OPA Properties Cleaned tables to build a Property History Dimension table? (Just asking the question). If so, then I may have an idea on how to solve it.