Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

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

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

4 Replies
tresesco
Not applicable

Re: Slowly Changing Dimensions - Discounts and Time Periods

Try using autonumber() for long concatenated fields like:

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

jontydkpi
Not applicable

Re: Slowly Changing Dimensions - Discounts and Time Periods

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable

Re: Slowly Changing Dimensions - Discounts and Time Periods

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.

Not applicable

Re: Slowly Changing Dimensions - Discounts and Time Periods

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