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

Deed Transfer Records Cleaned.yxdb

Property History Dimension.yxdb

Sample Table.xlsx

Datasets

Designer Cloud

Custom Tools

Accepted answers

ddiesel

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

All comments

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.

binu_acs

@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

Updating Records.yxmd

jly3292014

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

The finished table should have the following structure where PropertyID is a surrogate primary key (reference from sample table csv)
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.

Property History Dimension.yxdb

Deed Transfer Records Cleaned.yxdb

OPA Properties Cleaned.yxdb

Sample Table.xlsx

ddiesel

Hi @jly32920141!

I think it would be best if you keep working on your workflow. If you get stuck on a specific part, we can help you with the concept or point you in the direction of supplementary training materials.

You're are doing great so far! If you read your questions above, each time you were already on track with the right tools to solve the problem. Sometimes it takes a bit of trial and error.

I train new Alteryx users within my organization. I always have them start with the interactive lessons here:

https://community.alteryx.com/t5/Interactive-Lessons/tkb-p/interactive-lessons?lessons=all

I highly recommend these lessons! If you run through all the videos marked CORE, I think it will help you a lot.

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

AdamR 335