Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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);
No, Just wanted the expected output in excel format (enter manually)
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 |
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);