topic How to create a daily record automatically with script? in QlikView

How to create a daily record automatically with script?

Thu, 08 Oct 2015 06:55:32 GMT

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;

Re: How to create a daily record automatically with script?

Thu, 08 Oct 2015 13:27:50 GMT

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

;

Re: How to create a daily record automatically with script?

Fri, 09 Oct 2015 02:40:53 GMT

An error message appears.