4 Replies Latest reply: Jul 4, 2014 9:10 AM by H W RSS

    Slowly Changing Dimensions - Discounts and Time Periods

      Hi,

       

      I'm new to the scripting side of Qlikview and I've been trying to create a report that looks at RRP compared to actual selling price achieved. To do this it needs to match the Item Code to the price list for a given date range.

       

      I have the following script (that works with a small amount of data), but when I put c2m rows through sales and 100k rows through price it just crashes with the inconsistency type D error. Is there a better way of doing this?


      Script as below:

       

      Pricing:

      LOAD [Price Start Date],

           [Price End Date],

           ItemCode as [PricingItemCode],

           [List Price],

           [ItemCode] & '|' & [Price Start Date] & '|' & [Price End Date] as [Item+Interval]

      FROM

      [C:\Users\Desktop\QV Testing\PriceList.xlsx]

      (ooxml, embedded labels, table is Sheet1);

       

      Sales:

       

      LOAD OrderDate, PriceList, Sales, ItemCode as tmpItemCode,

      ItemCode &'|' & [OrderDate] as [Item+OrderDate]

      FROM

      [C:\Users\Desktop\QV Testing\Sales.xlsx]

      (ooxml, embedded labels, table is Sheet1);

       

      TmpBridgeTable:

      IntervalMatch (OrderDate, tmpItemCode)

      Load distinct [Price Start Date], [Price End Date], [PricingItemCode] as tmpItemCode

        Resident Pricing;


        BridgeTable:

      Load

        tmpItemCode & '|' & OrderDate as [Item+OrderDate],

        tmpItemCode & '|' & [Price Start Date] & '|' & [Price End Date] as [Item+Interval]

        Resident TmpBridgeTable;

       

      Drop Field tmpItemCode;

      Drop table TmpBridgeTable;

       

      Thanks in advance for your help.