5 Replies Latest reply on Jan 26, 2016 6:17 AM by renske.hercules.0

# LOOKUP using 2 datasets, including dates and strings

Hi All,

I am trying to create a calculated field where I want to lookup a string value from a certain column in Dataset 1 and show it in Dataset 2.

This is how it should work:

STEP 1:

It should compare titles In Dataset 1 with Dataset 2. The titles are written differently in both datasets. Therefore I created a calculated field twice: LOWER(Dataset 1) and LOWER(Dataset 2) so it would match the titles correctly.

STEP 2:

It should look at the date shown in dataset 2 and compare it to the startdate and enddate of dataset 1 to check if it falls within those dates. So DATE (dataset1) >= STARTDATE (dataset 2) AND DATA (dataset1) <= ENDDATE (dataset 2).

STEP 3:

If all conditions are met, it should look at Series Number and show it in Dataset 2, If not it should show NULL or a string like "Not available".

I attached dataset 1 and dataset 2 to this post and a workbook for anyone whos interested .

Who is willing to help me??

Thanks a lot!!!

Best,

Renske

• ###### 1. Re: LOOKUP using 2 datasets, including dates and strings

Anyone?

• ###### 2. Re: LOOKUP using 2 datasets, including dates and strings

Renske,

I'm not sure I understand your request correctly, but anyways....

Looked at your data, but Dataset 2 sometims has two start/end dates.

Then, it does not bring expected result.  ( ie: Drunking Dunking)

If the data has only one "Date set" for a title, I think it works.

Create calculated filed in both dataset

Title(Lower)

lower([Title])

Title(Lower)

lower([International Title])

Create Data Relationships

Create calculated field.

[Should show(calc)].

if attr([Date]) >= ATTR([Dataset1].[Start Date]) and attr([Date])<=ATTR([Dataset1].[End Date]) then 1 END

Some data still does not match, but I don't know the reason because the "Should Show" field is not calculation in your original data..

Thanks,

Shin

• ###### 3. Re: LOOKUP using 2 datasets, including dates and strings

Hi Shinichiro,

This isn’t really what Im looking for. It should first lookup the Titles, and then lookup a series number with conditions for a daterange. For example:

Behuku is shown in Dataset 2 > now it should check if its available in column international title from dataset 1 as well.

If not: NULL

If yes: Look further

Then it should compare the date shown in dataset 2. For example, the first date is 06-01-2015 > is it bigger or the same as the start date AND is it smaller or the same as the end date?

Yes > cause 06-01-2015 is bigger than 02-01-2015 and smaller than 13-02-2015.

If Not: NULL

If yes: show series number in dataset 1. In this case: it should show 7.

Does this make sense?

Best,

Renske Hercules

Research Analyst, Research & Insight

•: +31 (0) 20 893 9428 (direct) |•: Renske.Hercules@endemolshine.com<mailto:Renske.Hercules@endemolshine.com>

Mediarena 1 | 1114 BC | Amsterdam-Duivendrecht (Amsterdam Z-O)

P.O. Box 12133 l 1100 AC l Amsterdam

•: +31 (0)20 893 9000

• ###### 4. Re: LOOKUP using 2 datasets, including dates and strings

Renske,

I think I l already did what you said. looks like description was old one.

Create calculated field.  in the reply

[Should show(calc)].

if attr([Date]) >= ATTR([Dataset1].[Start Date]) and attr([Date])<=ATTR([Dataset1].[End Date]) then 1 END

Create calculated field.  in the workbook.

[Should show(calc)].

if attr([Date]) >= ATTR([Dataset 1 (Dataset 1)].[Start Date]) and attr([Date])<=ATTR([Dataset 1 (Dataset 1)].[End Date]) then ATTR([Dataset 1 (Dataset 1)].[Series]) END

Thanks,

Shin

• ###### 5. Re: LOOKUP using 2 datasets, including dates and strings

Hi Shin,

This is not working. It should lookup the titles first. Cause with this formula it will also show serienumbers when a date from Title 1 (in dataset2)  accidently falls in a date range from a Title 2 (in dataset 1). It should first check the title and THEN look at the rows..

Best,

Renske Hercules

Research Analyst, Research & Insight

•: +31 (0) 20 893 9428 (direct) |•: Renske.Hercules@endemolshine.com<mailto:Renske.Hercules@endemolshine.com>

Mediarena 1 | 1114 BC | Amsterdam-Duivendrecht (Amsterdam Z-O)

P.O. Box 12133 l 1100 AC l Amsterdam

•: +31 (0)20 893 9000