MAXIF equivalent

[klambert]

Hello All!

I currently have two tables, "scan" and "contract".

The scan table contains transactional information such as someone performing a scan at 2pm on the 24th Jan.

The contract table contains all the contract information such as the scan fee to be charged.

But it also has a from date, so if I have a contract from 21st Jan and it is superceded by a contract from the 25th Jan I want to be able to pull the associated fee from the contract date that covers that time period. eg the contract from the 24th.

If I was doing this in excel I would say return the value in the scan fee column where the contract date is the max of all the contract dates less than or equal to the scan fee date. In alteryx I am not even certain where to start!

Contract table:

Scan Table:

Thank you!

[MarqueeCrew]

@klambert ,

This is where to start!

1. MULTI-ROW FORMULA
2. GENERATE ROWS
3. JOIN

You'll use Multi-Row formula to create FROM-TO range.  Next, Generate rows to create a formula to create records for each "From" date.  Then join the data to the scan on DATEs.

Within the Generate Rows you'll need to change the data type to dates and use the DATETIMEADD() function with "days".

Cheers,

Mark

[MarqueeCrew · Accepted solution]

@klambert ,

I put this into a workflow to help you get your challenge solved.

Cheers,

Mark

[klambert]

Thank you!

Is there another alternative at all?

I have dates from 2003 across 120 different sites, which would make my generate rows quite large?

[MarqueeCrew]

Not too large for alteryx!