Slowly Changing Dimensions - Discounts and Time Periods

[#1]

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

(ooxml, embedded labels, table is Sheet1);

Sales:

LOAD OrderDate, PriceList, Sales, ItemCode as tmpItemCode,

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

FROM

(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.

[tresesco]

Try using autonumber() for long concatenated fields like:

AutoNumberHash128( [ItemCode] , [Price Start Date], [Price End Date])  as [Item+Interval]

[jonathandienst]

Hi

This is the way I normally do it. No need for a bridge table, just join the list price into the Sales table:

Pricing:

LOAD [Price Start Date],

     [Price End Date],

     ItemCode,

     [List Price]

FROM

(ooxml, embedded labels, table is Sheet1);

Sales:

LOAD OrderDate,

  PriceList,

  Sales,

  ItemCode

FROM

(ooxml, embedded labels, table is Sheet1);

Join (Sales)

IntervalMatch (OrderDate, ItemCode)

Load distinct [Price Start Date],

  [Price End Date],

  ItemCode

Resident Pricing;

Join (Sales)

LOAD [Price Start Date],

  [Price End Date],

  ItemCode,

  [List Price]

Resident Pricing;

DROP Field [Price Start Date], [Price End Date] From Sales;

DROP Table Pricing;

If you still run out of memory (that's usually what causes the error you saw), then you don't have enough memory for the joins on this data set...

HTH

Jonathan

[#4]

Thank you tresesco for the reply. I tried and unfortunately it didn't seem to change anything; it still got stuck at the same record.

[#5]

Hi Jonathan,

Thanks for the reply.

I tried that method but the same problem. Is there any way to get around the memory issue? I can't believe what i'm trying hasn't been done successfully before