topic Re: Slowly Changing Dimensions - Discounts and Time Periods in QlikView

Slowly Changing Dimensions - Discounts and Time Periods

Fri, 04 Jul 2014 10:52:57 GMT

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.

Re: Slowly Changing Dimensions - Discounts and Time Periods

tresB — Fri, 04 Jul 2014 10:57:23 GMT

Try using autonumber() for long concatenated fields like:

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

Re: Slowly Changing Dimensions - Discounts and Time Periods

jonathandienst — Fri, 04 Jul 2014 11:13:17 GMT

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

Re: Slowly Changing Dimensions - Discounts and Time Periods

Fri, 04 Jul 2014 13:09:19 GMT

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.

Re: Slowly Changing Dimensions - Discounts and Time Periods

Fri, 04 Jul 2014 13:10:21 GMT

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