topic Re: Problem with Applymap in QlikView

Problem with Applymap

Wed, 25 Nov 2020 16:16:04 GMT

I have a problem using Applymap to populate Jan2017 table with a field Country. Is there anything wrong with my script?

MapCountrytoPortfolio:

Mapping LOAD Portfolio,

Country

FROM

(biff, embedded labels, header is 1 lines, table is PortfolioStructure$);

Jan2017:

LOAD DAY,

TRADENBR,

ENTITYID AS Entity,

NODEID AS Portfolio,

PRODUCT_TYPE AS Product,

PRODUCT_ID AS Instrument,

STRATEGY,

COUNTERPART,

CCY,

TRADE_OCY AS [Sales Margin OCY],

SPOT_FX,

SPOT_RATE,

TRADE_LCY AS [Sales Margin LCY],

TYPE,

Month(DAY) AS Month,

Dual('Q' & Ceil(Month(DAY)/3), Ceil(Month(DAY)/3)) AS Quarter,

Year(DAY) AS Year,

Applymap('MapCountrytoPortfolio', Portfolio,'NA') as Country

FROM

(ooxml, embedded labels, table is [Sales Margin Report by Budget R]);

Re: Problem with Applymap

jhoef-pv — Thu, 25 May 2017 13:09:57 GMT

Hi,

Jan2017:

LOAD DAY,

TRADENBR,

ENTITYID AS Entity,

NODEID AS Portfolio,

PRODUCT_TYPE AS Product,

PRODUCT_ID AS Instrument,

STRATEGY,

COUNTERPART,

CCY,

TRADE_OCY AS [Sales Margin OCY],

SPOT_FX,

SPOT_RATE,

TRADE_LCY AS [Sales Margin LCY],

TYPE,

Month(DAY) AS Month,

Dual('Q' & Ceil(Month(DAY)/3), Ceil(Month(DAY)/3)) AS Quarter,

Year(DAY) AS Year,

Applymap('MapCountrytoPortfolio', NODEID ,'NA') as Country

FROM

(ooxml, embedded labels, table is [Sales Margin Report by Budget R]);

Regards

Re: Problem with Applymap

sunny_talwar — Thu, 25 May 2017 13:15:27 GMT

Doesn't look like it... are you getting an error?

Re: Problem with Applymap

vamsee — Thu, 25 May 2017 13:16:17 GMT

Applymap('MapCountrytoPortfolio', Portfolio,'NA') as Country

Applymap('MapCountrytoPortfolio', NODEID, 'NA') as Country

Re: Problem with Applymap

ahaahaaha — Thu, 25 May 2017 13:19:20 GMT

Hi,

I think that if earlier the script worked correctly, need to analyze the downloadable data

Re: Problem with Applymap

fkeuroglian — Thu, 25 May 2017 14:52:24 GMT

What error show when you reload?

Re: Problem with Applymap

praveen_prithiviraj — Thu, 25 May 2017 15:18:49 GMT

Applymap('MapCountrytoPortfolio',NODEID,'NA') as Country

Re: Problem with Applymap

omkarvamsi — Thu, 25 May 2017 15:36:28 GMT

try DISTINCT in Mapping Load ...

LOAD Distinct

Portfolio,

Country

FROM

(biff, embedded labels, header is 1 lines, table is PortfolioStructure$);

Re: Problem with Applymap

Marcellino_Groothof — Fri, 26 May 2017 06:29:21 GMT

Hi,

Try this: change Mapping LOAD Portfolio in

Mapping LOAD NODEID

and change

Applymap('MapCountrytoPortfolio', Portfolio,'NA') as Country in

Applymap('MapCountrytoPortfolio', NODEID,'NA') as Country

Greetings,

Marcellino

Re: Problem with Applymap

alis2063 — Fri, 26 May 2017 14:01:02 GMT

HI Yaron,

Kindly use the below,

MapCountrytoPortfolio:

Mapping LOAD distinct Portfolio,

Country