8 Replies Latest reply: May 15, 2014 10:13 AM by Olly Bond

External Lookup problem - 1:1 Relation

I've got 2 files which I like to combine.

The colums of the fields are separated in the example below with "|"

1. File:

Art-Nr | Date

2.File:

Art-Nr | Date Beginn (Period)| Date End (Period) | value

In both files are duplicates of the "Art-Nr".

In the 2. File are for duplicates of the Art-Nr different periods (Date Beginn (period) | Date End (Period) with different values

Now I like to have as a lookup the corresponding "value".

How can I give Monarch the information which Relation is the correct value ?

Art-Nr  <-> Art-Nr

Date  <-> within Date Begin (period) <->Date End (Period)

• External Lookup problem - 1:1 Relation

Hello Oli

I raised this in the Ask The Experts session in Vegas last week I've had the same challenge and solved it with a three pass solution using dummy multi-column regions. It would be a nice feature for a future release of Monarch if you could handle it with a lookup based on an inequality.

I'm planning to use the fix as my next blog post next week - can you hang on until then or do you want me to step you through it before that?

MfG

Olly

• External Lookup problem - 1:1 Relation

Hi Oli,

if you can give me an overview I can try find a solution the next days ? Would be great...

• External Lookup problem - 1:1 Relation

Hello Oli

Basically, you need to create one row of detail for each date in the range of the file with the Begin and End dates. You achieve this by opening it as a Report, not as a Database, and by using the Multicolumn Region feature to force Monarch to create n lines for each single line in the report. Depending on your data - is it all in one calendar year? Do you have more than 40 x 40 = 6400 days between a Begin date and an End date? - then there are a couple of options. A first pass with 12 columns then one on the output of that with 31 will let you handle ranges up to a year, for example.

From the second output, filter out any invalid dates (31 Feb) and any dates outside the range. Then export to MDB and use it as a normal lookup.

Hope this helps,

Olly

• External Lookup problem - 1:1 Relation

...sounds complicated, I try but I don't think that I can do this successfully.

Oli

• External Lookup problem - 1:1 Relation

Hallo Oli

Sie brauchen kein Angst haben. Morgen ist Himmelfahrt, Aber arbeiten Sie am Freitag?

I can take you through it by phone if that helps.

In the meantime, search here for variable width multi-columns to get an idea of the technique.

MfG

Olly

• External Lookup problem - 1:1 Relation

Hi Ollly,

we could phone on friday. When is it possible for you ? In the meantime I can send you the 2 files with the description. Is this ok ?

• External Lookup problem - 1:1 Relation

Hello Oli

Sure - email me the files. My handy number is in my email.

Mfg

Olly

• External Lookup problem - 1:1 Relation

Hello Olli, hello everyone,

As I mentioned, this theme cropped up in Vegas at the Ask the Experts session, and I thought it was worth trying to explain the solution in a bit more detail. As I can't post screenshots here, I've made a blog entry on "Invisible Data" at http://blog.greenbar.info/post/23790457456/invisible-data[/url] which takes you through a simplified example using Classic data.

Best wishes, mfG,

Olly