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);
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,)
Hi,
Can you post your Error?
In the above script, there is no error, but when I add the concat fields to the lookup, I get the below
May be try the same in the second table also..
LOOKUP([Target Date], Project&' '&ref, Dependent, 'DependencyList') as Dependency1TargetDate,
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?
Hi Kieran,
Can you provide the sample data with expected output?
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 |
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..
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 |