Lookup() a concatenated field

[#1]

Hi there

I have the below script. Currently, I am loading ''ref' into my mapping table. I would like to be able to load the concatenation of Project and Ref (Project&' '&ref as newref).

This does not work and I receive load errors,

Can anyone help?

Thanks

DependencyList:

LOAD ref, Dependent, [Target Date]

from (ooxml, embedded labels, table is [Risk]);

LOAD Project,

ref,

Project&' '&ref as newref,

Project&' '&Dependent as newdependency,

     [Risk Date Raised],

     [Raised by],

     Description,

     [Risk Owner],

     [Mitigation/ Contingency Actions],

     Probability,

     LOOKUP([Target Date], ref, Dependent, 'DependencyList') as Dependency1TargetDate,

     LOOKUP([Target Date], ref, LOOKUP(Dependent, ref, Dependent, 'DependencyList'), 'DependencyList') as Dependency2TargetDate,

     LOOKUP([Target Date], ref, LOOKUP(Dependent, ref, LOOKUP(Dependent, ref, Dependent, 'DependencyList'), 'DependencyList'),'DependencyList') as Dependency3TargetDate,

     [Impact Level],

     Status,

     Dependent,

     [Target Date],

     RAG

FROM

(ooxml, embedded labels, table is Risk);

[#2]

To clarify (sorry, not sure the above was clear),

I need to be able to use the mapping table & lookup to find the concat version of the field (Project&' '&ref as newref,)

[settu_periasamy]

Hi,

Can you post your Error?

[#4]

In the above script, there is no error, but when I add the concat fields to the lookup, I get the below

[settu_periasamy]

May be try the same in the second table also..

LOOKUP([Target Date], Project&' '&ref, Dependent, 'DependencyList') as Dependency1TargetDate,

[#6]

I've tried the below:

This now throws no errors, but the script does not work as I'd expect it to - it doesn't seem to be using the mapping table values.

I need to be able to include 'as variableName' - and use that name in the lookup formula.

Is this possible?

[settu_periasamy]

Hi Kieran,

Can you provide the sample data with expected output?

[#8]

Hi there,

The expected output is the target date of the project thats been looked up.

E.G.

Ref   dependency   target date

1       2                   01/01/2016
2       3                   12/01/2016

The lookup should take the target date of the dependency. So when looking up ref 1, you should get the output date of 12/01/2016.

THis is working before I concat the fields

I've attached a sample excel below.

Project

ref

Risk Date Raised

Raised by

Description

Risk Owner

Mitigation/ Contingency Actions

Probability

Impact Level

Status

Dependent

Target Date

RAG

5.1

R1

27/05/2015

Dave C

Task 1 details

Dave C

Risk Closed. Projects have not produeced PCR in time. PCRs to be evaluated on a case by case basis. Any that impact time will be bought to SteerCo for evaluaiton

Med

High

Open

01/09/2019

GREEN

5.1

R2

27/05/2015

Dave C

Task2 details

Dave C

Early planning with the DAB 4.2 project to minimise disruption

Low

High

Open

R1

01/01/2017

GREEN

5.1

R3

07/07/2015

Pete M

Task 3 details

Dave C

Risk accepted. Escalation may be required to ensure project timescales are met

Med

High

Open

R2

01/01/2012

GREEN

5.1

R4

07/07/2015

Pete M

Task 4 details

Dave C

Risk accepted. Escalation may be required to ensure project timescales are met

Med

High

Open

R3

01/01/2012

AMBER

5.1

R5

20/10/2015

Dave C

Task 5 details

Dave C

IBM to confirm if they can produce sample files ealier

Med

High

Open

R4

01/01/2011

RED

[settu_periasamy]

Hi,

There is no matching values between your first and second table, right?

1st table Ref values shows ,  1 , 2  , 3 like that.

2nd table Ref values has R1, R2 , R3

I just changed the 1st table value as R1 and R2.. it is working fine.

May be check the attachment..

[#10]

Hi there,

Thanks for the reply. In the table I pasted above, there are the same values - have pasted again below

Not sure what the issue is!

Project	ref	Dependent
5.1	R1
5.1	R2	R1
5.1	R3	R2
5.1	R4	R3
5.1	R5	R4