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
