Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Try using autonumber() for long concatenated fields like:
AutoNumberHash128( [ItemCode] , [Price Start Date], [Price End Date]) as [Item+Interval]
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
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.
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