Fill In Missing Rows

[#1]

I have a table that looks like this:

Color      Style_ID    Week

Blue        1               1

Blue        1               3

Green      1               1

Green      1               3

White      2               1

White      2               3

I want to add the missing week 2. How do I go about adding a week 2 for every style/color?

[sunny_talwar · Accepted solution]

May be this:

Table:

LOAD *,

  AutoNumber(Color&Style_ID) as Key

INLINE [

    Color, Style_ID, Week

    Blue, 1, 1

    Blue, 1, 3

    Green, 1, 1

    Green, 1, 3

    White, 2, 1

    White, 2, 3

];

FinalTable:

NoConcatenate

LOAD Color,

  Style_ID,

  Key,

  Week + IterNo() - 1 as Week

While Week + IterNo() - 1 <= WeekEnd;

LOAD *,

  If(Key = Previous(Key), Previous(Week), Week-1) as WeekEnd

Resident Table

Order By Key, Week desc;

DROP Table Table;

[#3]

Thank you, that worked.