1 Reply Latest reply: Oct 5, 2016 2:39 PM by Settu Periyasamy RSS

    Apply 7 MappingTables in a Measure

    Court van de Lisdonk

      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 [C:\QlikView Development\Eco Schulte\datasources\qvd\HstDat_SalesInvoice.qvd] (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