Multi row formula used more than once

jly3292014

Scenario: If it is assumed that all transactions are recorded, then the sale_date and sale_price of a property in the OPA Properties Cleaned table should be the most recent recorded document_date and total_consideration in the Deed Transfer Records Cleaned table. Find all properties whose sale_date and sale_price need to be updated.

So far, I was able to join the two tables together (by parcel_number), and then sort both parcel number (in ascending order) and recording date (in descending order). My next move would be to use the multi-row formula, group by parcel number, and then set the number of rows to two. That's how far I have gotten.

I am currently lost on how I can use the logic expression using four columns, probably using the if else statement.

Please provide feedback on how I can resolve the issue

OPA Properties Cleaned.yxdb

Deed Transfer Records Cleaned.yxdb

Designer Cloud

Accepted answers

DataNath

@jly3292014 grouping is done in multi-row formulas when you are comparing data across multiple rows, but you may want to keep said comparisons within a certain cluster (i.e. regions in a table with data for North/East/South/West). In your scenario here, you are comparing data across the table (on an individual row level) and so you don't need to group multiple rows together for any reason.

As to point number 2, this doesn't make sense and would cause everything to flag. The '!=' means 'Does not equal', so if you were to add in that expression, you would flag every row where the sale price doesn't equal the date, which would be the case in every record.

Any other questions, feel free to ask!

All comments

DataNath

Is this what you're after? If so, this could actually just be done in a regular formula tool after the join. It creates a flag if either the sale date isn't the same as document date, or if sale price isn't equal to the total consideration. As it's just row level, there's no need to group by.

Sale and price check against join.yxmd

jly3292014

Wow! That was quick.

Based on the scenario from the first message, I have a couple more questions:

1) How come I don't need to group anything?

2) Should I also include the following in the expression: [sale_date]!=[total_consideration] OR [sale_price]!=[document_date]? Why or why not?

DataNath

No problem! Feel free to mark the solution if that's sorted your issue, or continue asking away so we can help you further!

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703