Updating Records

[jly3292014]

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

[ddiesel]

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.

[jly3292014]

@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.

[ddiesel]

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.

[jly3292014]

@ddiesel I attached the workflow

[binuacs]

@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

[ddiesel]

Hi again @jly3292014  !

Wow! There’s a lot going on here. Here are a few things to consider:

• Before the Join, you need to consider how you will handle duplicates. There are duplicate records in both the Property History file and the Deed Transfer file. This is resulting in a Cartesian join (one record matches to many, causing further duplication of your parcel_number records)
• @binuacs  is right. The summarize is unnecessary, but your formula is working.
• I made one small change to the Multi-Row formula to change “” to null. This resolves the conversion errors.
• IF [parcel_number]=[parcel_number] THEN (IF [document_date]=[Max_document_date] THEN null() ELSE DateTimeAdd([Row+1:document_date],-1,"days") ENDIF) ELSE null() ENDIF
• Alternatively, if you remove the summarize, this formula that @binuacs and I suggested would be more efficient: DateTimeAdd([Row+1:document_date],-1,'day')

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

[jly3292014]

@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.

[ddiesel · Accepted solution]

@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.

[jly3292014]

@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:

1. The finished table should have the following structure where PropertyID is a surrogate primary key (reference from sample table csv)
2. 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
   1. 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.
   2. The owner of a transaction should be the Grantees
   3. 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.

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.