Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On May 18th at 10AM EDT we will answer your QlikView questions live. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
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
MVP
MVP

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

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

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
Author

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