topic Re: Fill In Missing Rows in QlikView

Fill In Missing Rows

Wed, 25 Nov 2020 16:16:04 GMT

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 — Tue, 16 Aug 2016 16:27:45 GMT

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;

Tue, 16 Aug 2016 16:46:47 GMT

Thank you, that worked.