Apply 7 MappingTables in a Measure

pacoli2013 — Wed, 05 Oct 2016 09:06:40 GMT

Hello,

In my script I have a tab In - and Excluding Data, there I have created 7 MappingTables from an Excelfile with 7 Sheets. On another Tab, called Measure, I load a masterfile HstDat_SalesInvoice.qvd] into a Table HstDat_SalesInvoice.

TAB In - and Excluding Data looks like:

/*
In - and Excluding Data : Data which sets limits on the Tables
and are mappingtables for the measure-table HstDat_SalesInvoice
*/
MapIncludinCompanys:
Mapping Load
Active,
including_Company
From
[$(DATADIR)Excel\Umsatzbildung001.xlsx]
(ooxml, embedded labels, table is including_Companys)
WHERE Active = 1;

MapExcludingCustomers:
Mapping Load
//        COMET_Active,
AX_SellToCustomerNumber,
// COMET_Debitorennr,
AX_Active
// ReasenNotInVC
From
[$(DATADIR)Excel\Umsatzbildung001.xlsx]
(ooxml, embedded labels, table is excluding_Customers)
WHERE (AX_Active)=1;

MapExcludingItemsAXComet:
Mapping Load
AX_ItemNumber,
// COMET_Artikelnr,
AX_Active
// COMET_Active,
//     ReasenNotInVC
FROM
[$(DATADIR)Excel\Umsatzbildung001.xlsx]
(ooxml, embedded labels, table is excluding_Items_AX_Comet)
WHERE (AX_Active)=1;

MapExcludingInvoicenumber:
Mapping Load
COMET_Active,
InvoiceNumber
// InvoiceNumberPos,
//     [(InvoiceNumber_InvoiceNumberPos]
//        [(InvoiceNumber_InvoiceNumberPos]                As       ExclInvoiceNumber_InvoiceNumberPos
FROM [$(DATADIR)\Excel\Umsatzbildung001.xlsx]
(ooxml, embedded labels, table is excluding_Invoicenumber_pos)
WHERE (COMET_Active        )=1;

MapExcludingItems:
Mapping Load
Active,
AX_ItemNumber
FROM
[$(DATADIR)excel\Umsatzbildung001.xlsx]
(ooxml, embedded labels, table is excluding_Items)
Where Active = 1;

MapExcludingLedgerVoucher:
Mapping Load
Active,
//        Company,
           LedgerVoucherNumber
FROM
[$(DATADIR)excel\Umsatzbildung001.xlsx]
(ooxml, embedded labels, table is excluding_LedgerVoucher)
where Active = 1 ;

MapIncludingRevenueAccounts:
Mapping Load
AX_GLAccountNumber,
Active
FROM
[$(DATADIR)excel\Umsatzbildung001.xlsx]
(ooxml, embedded labels, table is including_RevenueAccounts)
Where      Active = 1;

TAB Measure looks like:

HstDat_SalesInvoice:

LOAD
Company &' '& DataLevel as DimHierPrimDimCompanyKey,

Company,
DataLevel,
LoadingDate,
InvoiceNumber,
InvoiceLineNumber,
CustomerID,
InvoiceAmount;
Load *,
ApplyMap('MapIncludinCompanys', Active, null())                     as ActiveIncludingCompany,
ApplyMap('MapExcludingCustomers', AX_Active, null())           as ActiveExcludingCustomers,
ApplyMap('MapExcludingItemsAXComet', AX_Active, null())    as ActiveExcludingItemsAXComet,
ApplyMap('MapExcludingInvoicenumber', COMET_Active, null()) as ActiveExcludingInvoicenumber,
ApplyMap('MapExcludingItems', Active, null())              as ActiveExcludingItems,
ApplyMap('MapExcludingLedgerVoucher', Active, null())          as ActiveExcludingLedgerVoucher,
ApplyMap('MapIncludingRevenueAccounts', Active, null())       as ActiveIncludingRevenueAccounts

FROM [$(DATADIR)qvd\HstDat_SalesInvoice.qvd] (qvd) ;

The LoadingLogging looks like:

Marker << INLD42F 2 lines fetched
PrimDef_DayGrid << PrimDef_DayGrid (qvd optimized) 4.018 lines fetched
MapIncludinCompanys << including_Companys 2 lines fetched
MapExcludingCustomers << excluding_Customers 7 lines fetched
MapExcludingItemsAXComet << excluding_Items_AX_Comet 4 lines fetched
MapExcludingInvoicenumber << excluding_Invoicenumber_pos 442 lines fetched
MapExcludingItems << excluding_Items 8 lines fetched
MapExcludingLedgerVoucher << excluding_LedgerVoucher 3 lines fetched
MapIncludingRevenueAccounts << including_RevenueAccounts 22 lines fetched

So the MappingTables are created, I have the lines fetched compared with the lines in the excelsheet, no difference there.

The problem is that I got an errormessage:

Field not found - <Active>

Load *,

ApplyMap('MapIncludinCompanys', Active, null()) as ActiveIncludingCompany,

ApplyMap('MapExcludingCustomers', AX_Active, null()) as ActiveExcludingCustomers,

ApplyMap('MapExcludingItemsAXComet', AX_Active, null()) as ActiveExcludingItemsAXComet,

ApplyMap('MapExcludingInvoicenumber', COMET_Active, null()) as ActiveExcludingInvoicenumber,

ApplyMap('MapExcludingItems', Active, null()) as ActiveExcludingItems,

ApplyMap('MapExcludingLedgerVoucher', Active, null()) as ActiveExcludingLedgerVoucher,

ApplyMap('MapIncludingRevenueAccounts', Active, null()) as ActiveIncludingRevenueAccounts

FROM (qvd)

The way of creating the MappingTables and the ApplyMap-statements I got from Internet/A QlikDesignBlog. The only difference is that I have 7 MappingTables stead 1, but I don’t think that’s the problem.

Changing the second LOAD from Load *, into Load is no solution,

Perhaps someone can help me

Thanks in advance

Re: Apply 7 MappingTables in a Measure

settu_periasamy — Wed, 05 Oct 2016 18:39:01 GMT

Hi,

Load *,
ApplyMap('MapIncludinCompanys', Active, null()) as ActiveIncludingCompany,

FROM [$(DATADIR)qvd\HstDat_SalesInvoice.qvd] (qvd) ;

The Active field (May be in some other name) should be available in your HstDat_SalesInvoice.qvd.

It will automatically lookup your 'MapIncludinCompanys'.

http://www.learnqlickview.com/applymap-function-in-qlikview/

topic Re: Apply 7 MappingTables in a Measure in QlikView

Apply 7 MappingTables in a Measure

Re: Apply 7 MappingTables in a Measure