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);

[settu_periasamy]

No, Just wanted the expected output in excel format (enter manually)

[#3]

Oh sure, thanks - so it's like this. Project + Ref = Ref 2

Project	ref	Ref2
5.3	R1	5.3R1
5.3	R2	5.3R2
5.3	R3	5.3R3
5.3	R4	5.3R4
5.3	R5	5.3R5

[settu_periasamy]

Hi Kieren,

May be try like this based on your excel data..( in the lookup 3 parameter should be match field value.

i just created 2 dependency date, you can create more date)

Directory;
DependencyList:
LOAD Project&' '&ref as FullProjectRef,
Project&' '&Dependent as dependentTask,
[Target Date]
FROM
Dependencies.xlsx
(ooxml, embedded labels, table is Risk) Where not isnull(Project);

New:
LOAD Project,
ref,
Description,
Lookup('Target Date','FullProjectRef',Project&' '&ref,'DependencyList') as Dependency1TargetDate,
Lookup('Target Date','dependentTask',Project&' '&Dependent,'DependencyList') as Dependency2TargetDate,
Status,
Dependent,
[Target Date],
RAG
FROM
Dependencies.xlsx
(ooxml, embedded labels, table is Risk) Where not isnull(Project);