5 Replies Latest reply: May 15, 2014 10:11 AM by Olly Bond

Compare Value to Range Question

I am working with a model where I pull information using ODBC.  I have a field that is Service Tech Number..  The service techs are assigned to teams and in an excel file, I have the range of tech numbers and the associated team they go with.

Here is a partial sample of what I have in excel file:

Team Name                   Tech Beginning Range     Tech Ending Range

Major Account Team                   16SE1A                 16SE1Z

Canon Team                           16SE2A                 16SE2Z

Wide Format                           16SE4A                 16SE4Z

The report would have tech number 16SE2B and I want to have a field that shows he is on the Canon Team.

I have done this using a calculated field and if statement, but the problem is, the techs switch teams a lot and someone is updating the ranges on the spreadsheet to show what team they are on.   I don't want to have to update the model all the time with the new ranges so I am trying to figure out how to do this within Monarch.

Any help would be appreciated.

Chuck

• Compare Value to Range Question

Hello Chuck

Yes, this can be done automatically, but with a couple of limitations. There are two possible tricks to use - one using the "Copy values from previous record" and the other defining an array using a dummy multiple column region. Is it safe to assume that the ranges are always from A to Z? Can you give an idea of how many ranges there are?

Best wishes

Olly

• Compare Value to Range Question

Hi Chuck

It might be that I'm not understanding (my fault). But have you tried to use an external lookup to Excel and then you have many options to update dynamically in Excel  so when you refresh your monarch model the correct team name appears based on the tech number. The calculated field can be done in Excel:

One Approach is 16SE2 represents Cannon then [U]create a join based the first 5 position /Uof the service tech number /Uand an excel file that has all the unique first 5 positions and the associated name in column B.

ie column A = 16SE2 * column B  = Cannon  next row column a = 16SE4*  column b =  "XYZ corporate"

Am i on the right path.

John

• Compare Value to Range Question

you might be, but  I am not seeing it..

Hi Chuck

It might be that I'm not understanding (my fault). But have you tried to use an external lookup to Excel and then you have many options to update dynamically in Excel  so when you refresh your monarch model the correct team name appears based on the tech number. The calculated field can be done in Excel:

One Approach is 16SE2 represents Cannon then [U]create a join based the first 5 position /Uof the service tech number /Uand an excel file that has all the unique first 5 positions and the associated name in column B.

ie column A = 16SE2 * column B  = Cannon  next row column a = 16SE4*  column b =  "XYZ corporate"

Am i on the right path.

John[/QUOTE]

• Compare Value to Range Question

Hey olly,

There are about 25 different ranges that are associated with a team name.  The first 4 positions of the Ranges will be the same on the same team, meaning if the beginning range is 16SE then the ending range for that same team will be 16SE.

I have no idea about defining an array using aa dummy multiple column region and don't see how the Copy Values from previous record would help.  Sorry, guess I am just blind on this one.

Thanks for the help.

Hello Chuck

Yes, this can be done automatically, but with a couple of limitations. There are two possible tricks to use - one using the "Copy values from previous record" and the other defining an array using a dummy multiple column region. Is it safe to assume that the ranges are always from A to Z? Can you give an idea of how many ranges there are?

Best wishes

Olly[/QUOTE]

• Compare Value to Range Question

Hello Chuck,

Sorry if that wasn't clear. Dummy MCR would work like this - from your Excel file, save it as text and open in Monarch. Trap it as usual but with the multi column region active, defined with 26 columns, starting on position 100, with a column width of 1. Then in the table, define a calculated field using substr("ABCDEFGEHIJKLMNOPQRSTUVWXYZ";Column();1), which, together with the first five characters of the range, will give you a fully populated table so that your external lookup will find a match.

Hope this helps,

Olly