topic Re: Use of qlikview generated fields in QlikView

Use of qlikview generated fields

Wed, 08 Apr 2015 06:48:49 GMT

Hi all,

I have a simple question: I want to use the fields generated by qlikview. The script below illustrates what I have done. In this case, how can I make use of [Custodian Final] further in the script? What I want to do for example is combine the new field with another field by [Custodian Final]&"_"&[Trans. code].

StarMap:

LOAD [Trans. code],

[Security type],

Counterparty,

Operations,

From

LEFT Join (StarMap)

LOAD Operations,

[Custodian Final]

From

Thanks in advance!

Re: Use of qlikview generated fields

martinpohl — Wed, 08 Apr 2015 06:54:58 GMT

Hi,

for example use mapping / applymap

Map:

mapping

LOAD Operations,

[Custodian Final]

From

StarMap:

LOAD [Trans. code],

[Security type],

applymap('Map',Operations,'not found')&'_'&[Trans. code] as NewField

Counterparty,

Operations,

From

If there are more fields from the second table you have to reload the StarMap table:

StarMap2:

load

[Custodian Final]&"_"&[Trans. code] as NewField1,

.... as NewField2 and so on

Regards

Re: Use of qlikview generated fields

Wed, 08 Apr 2015 07:27:34 GMT

Thank you for the prompt reply.

However, I can't get StarMap2 to display. I am sure I am doing something wrong. Below is the script I am using. It is different from the example I have shown you before. In this example I can derive from the model portfolio or model portfolio code whether something is internal or external. In the end I want to combine the field [Code intern/extern] with the created fields [intern/extern 2] and [intern/extern 3].

Map:

mapping

LOAD [Model portfolio code],

[intern/extern 2]

FROM

[intern/extern 3]

FROM

[Model portfolio],

[Model portfolio name],

[Model portfolio code],

[Code intern/extern],

[Reconciliation ID],

applymap('Map',[Model portfolio code],'not found')&'_'&[Trans. code] as InternExtern,

applymap('Map2',[Model portfolio],'not found')&'_'&[Trans. code] as InternExtern2

FROM

&"_"&InternExtern&"_"&InternExtern2 as InternExtern3

;

Re: Use of qlikview generated fields

martinpohl — Wed, 08 Apr 2015 07:30:48 GMT

I missed something in my script (and you too)

StarMap2:

load

[Code intern/extern]&"_"&InternExtern&"_"&InternExtern2 as InternExtern3

resident StarMap

;

drop table StarMap;

Regards

Re: Use of qlikview generated fields

Wed, 08 Apr 2015 07:37:14 GMT

Hi,

I get the following error:

Field not found - <_>

StarMap2:

load

[Code intern/extern]&"_"&InternExtern&"_"&InternExtern2 as InternExtern3

resident StarMap

My script now looks like the following:

Map:

mapping

LOAD [Model portfolio code],

[intern/extern 2]

FROM

[intern/extern 3]

FROM

[Trans. code],

[Security No.],

[Security type],

Counterparty,

Custodian,

[Settlement date],

[Changed by user],

[Changed date],

[Created by user],

[Created date],

Portfolio,

[Model portfolio],

[Model portfolio name],

[Model portfolio code],

Custody,

[Custody account],

Currency,

Nominal,

[Instrument type],

[Transaction cancellation flag],

[Bank account],

Operations,

[Code intern/extern],

[Reconciliation ID],

applymap('Map',[Model portfolio code],'not found')&'_'&[Trans. code] as InternExtern,

applymap('Map2',[Model portfolio],'not found')&'_'&[Trans. code] as InternExtern2

FROM

&"_"&InternExtern&"_"&InternExtern2 as InternExtern3

resident StarMap

;

drop table StarMap;

Re: Use of qlikview generated fields

martinpohl — Wed, 08 Apr 2015 07:38:52 GMT

Don't use &"_"&

but &'_'&

Regards

Re: Use of qlikview generated fields

Wed, 08 Apr 2015 07:56:25 GMT

It works! Thank you very much for your help!