How to add extra Date rows in a table

[#1]

Hi,

     I have a Table with Columns: StockItem,TransactionDate,QuantityOnDate(StockItem Quantity On The Transaction Date),Bin,Warehouse.I have Created a key with StockItem,Bin and Warehouse.So for a particular key there will be a list of Transaction Dates and quantities on those dates,This repeats for second key value and so on.

Now I need to calculate The quantity for all dates from 2006 to 2010,i.e I have to add the Dates in between the Date rows.The Quantity will be  just the previous Quantity loaded,which i can do with The peek or previous function but how to add the missing Date rows in between.

For example for the first key, Quantity on 30/07/2010 is -22 and Quantity on 04/08/2010 is also -22.I need to add dates from 30/07/2010 to  04/08/2010 after  The first row such that the value is the Quantity of 30/07/2010.

I need to add Dates from 01/01/2006 to todays date for every key.I can Calculate the Values on those Dates but 'How to add the Dates!' in the script itself?

Any ideas Please? Thank You.

[#2]

any thoughts of which function i need to use in the script

[vijay_iitkgp]

Hi Kevin,

If I am correct You want to create the ledger of stocks. For this first you have to create a dummy  table which contains all the dates foe every key with 0 value and then concatenate it to your current transactional table. After concatenating you need to sort it on key and date and then use the peek function to call previous row value. Hope this will help you.

Regards

Vijay

[#4 · Accepted solution]

Thanks vijay.Your idea in good but since it is an older post i already sorted how to do it.I used Interval match function back then.