How to create line for every month (even when there is no transaction in that month)
Hi!
I am building QV app where we need information for every months last day's QTYOnHand and StandardCost information, I can get this information from transaction table.
Here is example when everything works easily (using monthend-function and max(transaction number)), because every month had at least one transaction. Transaction table holds information for QtyOnHand & StdCost also.
Now February and May line is not exist because there was not any transactions for these months in transaction table. But I want to get also those months with values on QtyOnHand & StdCost from last month with value.
ItemKey MonthEndDate QtyOnHand StdCost 101A|SE|1030 31.1.2009 20 45 101A|SE|1030 28.2.2009 20 45 <-no transactions, then values from last month with transaction(s) 101A|SE|1030 31.3.2009 27 42 101A|SE|1030 30.4.2009 11 38 101A|SE|1030 31.5.2009 11 38<-no transactions, then values from last month with transaction(s) 101A|SE|1030 31.6.2009 20 36
In sheet object I can use expression with above function, but how I can "store" this information for QV-table. I need these values in later calculations in the same QV application.
I see couple of answers already in this thread, but here is more information.
Here is an extract of the script
TrHist: //from here is all the data readed ... tr_effdate, date(tr_effdate,'YYYY-MM-DD') as CALDATE, Year(tr_effdate) as TRANSACTIONYEAR, Month(tr_effdate)as TRANSACTIONMONTH, Day(tr_effdate) as TRANSACTIONDAY, monthend(tr_effdate) as MONTH_END_DATE, tr_type, tr_trnbr, //transaction number
tr_begin_qoh, //quantity before transactiontr_qty_loc //transaction's change to quantity
ytr_price, ...
QOH_SumTemp1: // this table get's maximum transaction number / month load %%%ITEM_KEY, max(tr_trnbr) as max_trnbr, MONTH_END_DATE resident TrHist where (tr_type <> 'WO-CLOSE' and tr_type <> 'RJCT-WO') group by MONTH_END_DATE,%%%ITEM_KEY;
QTY_OH_Map: // here we get change of the quantity in stock Mapping load %%%ITEM_KEY &'|' & tr_trnbr as %%%mapkey , tr_begin_qoh + tr_qty_loc as QTY_OH resident TrHist;
STD_Price_Map: // here we get transaction price Mapping load %%%ITEM_KEY &'|' & tr_trnbr as %%%mapkey , tr_price resident TrHist;
QOH_Sum: load // %%%ITEM_KEY & '|' & MONTH_END_DATE as QTY_KEY, %%%ITEM_KEY, MONTH_END_DATE, applymap('QTY_OH_Map',%%%ITEM_KEY & '|' & max_trnbr,'N/A') as QTY_OH, applymap('STD_Price_Map',%%%ITEM_KEY & '|' & max_trnbr,'N/A') as STD_COST resident QOH_SumTemp1;
