Conditional filtering of a multirow table in relation to one-row table

[ROUD89]

Hi, 

I'm having a problem which I don't know how to solve most easily. I have a dataframe (DF1) and a vector (one-row dataframe, DF2). What I want to do is check per line item in DF1 if the value is above the value in the corresponding column in DF2.

Attached is a snippet of the two dataframe merged together. What I want to do is filter on those line items where 'Total Pawdacity Sale" is higher then  'LowerFence_PawdacitySales'. Using filter doesn't work with JOIN because it only takes into account the first row.

Can you do an IFTHENELSE with mutiple inputs? Or is there a way to copy he figures of the LowerFences variables over the other 10 rows?

many thanks

[Kanderson · Accepted solution]

Instead of stacking your datasets you should be able to use the append tool. This will add DF2 data to all the DF1 records. You can then create a column using the formula tool to assist with filtering.

[patrick_digan]

---

@ROUD89 wrote:

Or is there a way to copy he figures of the LowerFences variables over the other 10 rows?

 @ROUD89 In order to copy the figures down, I would use a multi-row formula tool, update the lowerfences field and then right a formula like:

IIF(isnull([Lower Fence_PawdacitySales]),[Row-1:Lower Fence_PawdacitySales],[Lower Fence_PawdacitySales])

That's In-line If statement that checks if the Lower Fence field is null. If it's null, it uses the previous row's value. If it's not null, then it doesn't make a change to that field. 

Let me know if that doesn't point you in the right direction!