Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am trying to do a dashboard for a restaurant and I am stuck in a problem on how to deal with the menus and each individual item from a menu to calculate the cost. I got two tables; one is ‘Item’, which is used to record the Item ID, unit cost of each item and the date of unit cost change. Another one is ‘Item_Result’, which is aim to record the daily cost changes of each item automatically
For example, in the original ‘Item’ table, there are altogether 3 records for” Item A” since the cost has changed thrice. And what I want to do is to create a daily record for every item to show the cost changes, just like what is shown in the pic below.
The problem is that I fail to create the record consecutively so that the daily record shown can carry on until today. Just like ic0002, since there is no change under the unit cost from the 'item' table, the script does not tend to generate records automatically. What is the problem of my script?
Item:
LOAD Item_ID,
Item_Name,
[Modified Date for cost],
[Unit Cost]
FROM
[Sample Data v0.1.xlsx]
(ooxml, embedded labels, table is Item);
TempTable:
load *, if(previous(Item_ID)=Item_ID, previous([Modified Date for cost]),[Modified Date for cost]+1) as NextDate
FROM
[Sample Data v0.1.xlsx]
(ooxml, embedded labels, table is Item)
order by Item_ID, [Modified Date for cost] desc;
DROP TABLE Item;
Item_Result:
Load Item_ID, date([Modified Date for cost] + IterNo() -1) as [Modified Date for cost],[Unit Cost]
Resident TempTable
while [Modified Date for cost] + IterNo() - 1 < NextDate
order by Item_ID, [Modified Date for cost];
DROP TABLE TempTable;
Try like below:
Item:
LOAD Item_ID,
Item_Name,
[Modified Date for cost],
Num([Modified Date for cost]) as TempDateKey,
[Unit Cost]
FROM
[Sample Data v0.1.xlsx]
(ooxml, embedded labels, table is Item);
Join(Item)
Load
Item_Name ,
MinDate + IterNo() - 1 AS TempDateKey
While Iterno() <= MaxDate - MinDate + 1
;
Load
Item_Name ,
Max([Modified Date for cost]) as MaxDate,
Min([Modified Date for cost]) as MinDate
Resident Item;
FinalItem:
Load
Item_Name ,
Date(TempDateKey) AS Item_Date,
IF(IsNull([Unit Cost]), Previous([Unit Cost]),[Unit Cost]) AS UnitCost
Resident Item
Order by Item_Name , TempDateKey
;
An error message appears.