ApplyMap from another ApplyMap Field

Wed, 15 Aug 2012 07:25:23 GMT

Hi QlikExperts,

Sorry for the lengthy code. I tried best to explain with inline comments. Is this possible?

//Table with Reporting Unit Data

RefReportingUnit:

LOAD InvoicingCode, ReportingUnit0, ReportingUnit0Code as ReportingUnit0Code1, Currency, ReportingUnitGroup

FROM D:\Robin_WS\BI-MS_EUR_URM_SGLL0199\QV\Data\Referentials\RefReportingUnit.qvd (qvd);

//Mapping table that will MAP the reporting unit to the Fact table, where this (ReportingUnit0Code) will be used to create a Composite Key

Map_ReportingUnit:

MAPPING LOAD InvoicingCode as Map_InvoicingCode, ReportingUnit0Code1 as Map_ReportingUnit0Code

Resident RefReportingUnit; drop Table RefReportingUnit;

//Mapping table that will MAP the reporting unit to the Fact table, where this (Customer3) will be used to create a Composite Key

Map_Customer3:

MAPPING LOAD BillingCustomerCode as Map_BillingCustomerCode, Customer3 as Map_Customer3

from D:\Robin_WS\BI-MS_EUR_URM_SGLL0199\QV\Data\Referentials\RefBillingCustomer.qvd (qvd);

//ApplyMap function used in the Fact Table, to bring the Reportingunit0Code and Customer3 fields

Fact:

LOAD *,

ApplyMap('Map_ReportingUnit',InvoicingCode) as map_REPORTINGUNIT0CODE,

ApplyMap('Map_Customer3',BillingCustomerCode) as map_Customer3

FROM (qvd);

//Create a mapping load, these are the Map fields that were created above. I am trying to use the mapped Customer3 field in below table.

Map_F_Customer3:

Mapping LOAD

map_REPORTINGUNIT0CODE as map_F_REPORTINGUNIT0CODE, map_Customer3 as map_F_Customer3

resident Fact;

//Finally using the Customer3 here. Composite keys from the fact table will be linked to the below table. Reason: Below extracted excel file is for defining user selection (for which selection user should see which data. Like an restriction to see certain values on selection of certain fields). Idea is if a field is mentioned then that will be used for selection, else if ALL is mentioned in the excel file, it should pick the possible values for selection. TO avoid mentioning all the possible combinations in the excel itself (just trying to save some rows, else the number of records will increase)

Reduction:

LOAD PROFILE,

REPORTINGUNIT0CODE as map_REPORTINGUNIT0CODE,

//if([Customer3 (Bill)]='ALL',ApplyMap('Map_F_Customer3',map_F_REPORTINGUNIT0CODE),[Customer3 (Bill)]) as [Customer3 (Bill1)],

ApplyMap('Map_F_Customer3',map_REPORTINGUNIT0CODE) as [Customer3 (Bill1)],

[Customer3 (Bill)],

CustomerIGOG,

CurrencyConversion,

Flag

FROM (biff, embedded labels, table is Sheet1$);

Any Suggestions?

Thanks,

Robinson

Re: ApplyMap from another ApplyMap Field

Wed, 15 Aug 2012 09:56:11 GMT

Hi,

In short I am trying to use an applymap field, further again as an applymap field. The error it throws: Field not Found "map_REPORTINGUNIT0CODE".... in the last table "Reduction"

Can we not use the ApplyMapped field just like any other field in another table?

Thanks,

Robinson

Re: ApplyMap from another ApplyMap Field

RedSky001 — Wed, 15 Aug 2012 15:34:26 GMT

Hi Robinson,

In the last statement replace

ApplyMap('Map_F_Customer3',map_REPORTINGUNIT0CODE) as [Customer3 (Bill1)

with

ApplyMap('Map_F_Customer3',REPORTINGUNIT0CODE) as [Customer3 (Bill1)

Mark

Re: ApplyMap from another ApplyMap Field

Thu, 16 Aug 2012 02:42:50 GMT

Hi,

As per my understanding, we should use the Mapping Load field (without aliase), which is here map_REPORTINGUNIT0CODE.

But even if if use the REPORTINGUNITOCODE, i get the following

Instead of IEOO_L00 in Customer3(Bill1), we should be having the values from map_Customer3, as I am trying the map the values ALL ,with map_Customer3 values.

Thanks,

Robinson

Re: ApplyMap from another ApplyMap Field

RedSky001 — Tue, 21 Aug 2012 13:53:24 GMT

Can you attach a sample application with the spreadsheets? (It would make it easier to understand the problem you are having I'm sure.)

Mark

topic Re: ApplyMap from another ApplyMap Field in QlikView

ApplyMap from another ApplyMap Field

Re: ApplyMap from another ApplyMap Field

Re: ApplyMap from another ApplyMap Field

Re: ApplyMap from another ApplyMap Field

Re: ApplyMap from another ApplyMap Field