topic Re: Lookup() a concatenated field in QlikView

Lookup() a concatenated field

Thu, 31 Mar 2016 07:56:09 GMT

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

Re: Lookup() a concatenated field

Thu, 31 Mar 2016 08:08:02 GMT

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

Re: Lookup() a concatenated field

settu_periasamy — Thu, 31 Mar 2016 08:12:11 GMT

Hi,

Can you post your Error?

Re: Lookup() a concatenated field

Thu, 31 Mar 2016 08:19:06 GMT

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

Re: Lookup() a concatenated field

settu_periasamy — Thu, 31 Mar 2016 08:24:23 GMT

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

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

Re: Lookup() a concatenated field

Thu, 31 Mar 2016 08:34:03 GMT

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?

Re: Lookup() a concatenated field

settu_periasamy — Thu, 31 Mar 2016 09:00:49 GMT

Hi Kieran,

Can you provide the sample data with expected output?

Re: Lookup() a concatenated field

Thu, 31 Mar 2016 09:05:15 GMT

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

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

27/05/2015

Dave C

Task2 details

Dave C

Early planning with the DAB 4.2 project to minimise disruption

Low

High

Open

01/01/2017

GREEN

5.1

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

01/01/2012

GREEN

5.1

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

01/01/2012

AMBER

5.1

20/10/2015

Dave C

Task 5 details

Dave C

IBM to confirm if they can produce sample files ealier

Med

High

Open

01/01/2011

RED

Re: Lookup() a concatenated field

settu_periasamy — Thu, 31 Mar 2016 09:47:51 GMT

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..

Re: Lookup() a concatenated field

Thu, 31 Mar 2016 09:55:32 GMT

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

Re: Lookup() a concatenated field

settu_periasamy — Thu, 31 Mar 2016 10:05:57 GMT

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..

Re: Lookup() a concatenated field

Thu, 31 Mar 2016 10:32:16 GMT

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

Re: Lookup() a concatenated field

settu_periasamy — Thu, 31 Mar 2016 11:28:23 GMT

Hi,

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

Re: Lookup() a concatenated field

Thu, 31 Mar 2016 12:16:49 GMT

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

Thanks

Re: Lookup() a concatenated field

settu_periasamy — Fri, 01 Apr 2016 01:49:14 GMT

Hi,

Script Attached..

Re: Lookup() a concatenated field

Mon, 04 Apr 2016 11:05:24 GMT

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!

Re: Lookup() a concatenated field

settu_periasamy — Mon, 04 Apr 2016 12:15:47 GMT

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)

Re: Lookup() a concatenated field

Mon, 04 Apr 2016 12:51:14 GMT

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

Re: Lookup() a concatenated field

settu_periasamy — Tue, 05 Apr 2016 10:27:30 GMT

Hi Kieran,

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

Re: Lookup() a concatenated field

Tue, 05 Apr 2016 10:31:14 GMT

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