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, I'm talking about the below one..

you have two table, one with target date( lookup table - Dependency List)

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

Is it possible to provide those with excel attachment? Let me just try..

or you can have a look the applymap() also instead of Lookup..

[#3]

Oh sorry, that one was just an example - the pasted Excel sheet was the data I am looking to use.

I've attached the document I am working with. Specifically, I am working with the Risks sheet. All calculations happen within that single table / sheet at present

[settu_periasamy]

Hi,

May be check the attachment. I tried with ApplyMap().

[#5]

Hi

Is it possible to show the code here? I have personal version, so can only open items using my key

Thanks

[settu_periasamy]

Hi,

Script Attached..

[#7]

Hi there,

Script doesn't seem to be attached

I've reached the below situation. I now get no errors and am using variables in the load script. However, the output is still not the same as when I manually join the two fields before loading.

Any advice would be great!

[settu_periasamy]

Hi,

Qvs file attached in my previous post .. you need open the thread in new window  (it won't display when see it from your inbox)

[#9]

Oh thanks - I've taken a look.

Unfortunately, I have to use Lookup as I use more than 2 columns.

I don't think I am far off with the script in my previous post, just something going wrong with variables

[settu_periasamy]

Hi Kieran,

Can you provide the Output column in excel ? i will try

[#11]

Hi there,

You mean, concatenate the two fields in Excel and try from there?

That does work - the issue is when I start using a variable in the Lookup() script