Entering dummy rows in a table

[bsharma_velcro]

Hi,

I need to insert some dummy rows in a table (at the back end) based on a condition.

Every month I get forecast data for current month and next 11 months. So, in January I have Forecast data from January till December. In February, I have Forecast from February to January next year...so on and so forth.

From February onwards, I need to insert dummy rows in my data so that no matter which month the forecast is getting created, I have "Forecast For Month" column with all the months for the current year. So, for February, I need to add 1 dummy row with 0 as Forecast value, for March, I need to add 2 dummy rows,.... as shown in the output.

Row Data:

Output:

Appreciate any help with the script.

Regards,

BS

[HirisH_V7]

Hi something like this,

TempRange:
LOAD
    Min(MonthStart(Date#(MonthName, 'MMM-YYYY'))) as MinMonth,
    Max(MonthStart(Date#(MonthName, 'MMM-YYYY'))) as MaxMonth
Resident Data;

AllMonths:
LOAD 
    Date(AddMonths(MinMonth, IterNo() - 1), 'MMM-YYYY') as Forecast_Month_Name
While AddMonths(MinMonth, IterNo() - 1) <= MaxMonth;
Resident TempRange

Left Join (AllMonths)
LOAD 
    Date#(MonthName, 'MMM-YYYY') as MonthDate,
    Forecast_Month_Name,
    <Rest other fields here>
Resident Data;
DROP Table TempRange;

[Daniel_Castella · Accepted solution]

Hi @bsharma_velcro 

Try this code, the output appears well to me:

A:
LOAD
"Creation Date",
"Forecast Date",
Value
FROM [lib://DataFiles/Inserting Dummy Rows.xlsx]
(ooxml, embedded labels, table is RawData);

B:
LOAD "Creation Date"
RESIDENT A;

OUTER JOIN (B)
LOAD "Creation Date" as "Forecast Date",
'0' as Value
RESIDENT A;

Concatenate (A)
LOAD *
RESIDENT B
Where num("Creation Date")>num("Forecast Date");

DROP TABLE B;

Kind Regards

Daniel