topic Re: How to identify and create period missing ? in QlikView

How to identify and create period missing ?

suzel404 — Wed, 23 Mar 2016 11:15:56 GMT

Hi all,

How can I identify and create missing period?

Thanks for your help.

Chanty4u — Wed, 23 Mar 2016 11:20:08 GMT

chk dis

sunny_talwar — Wed, 23 Mar 2016 13:17:36 GMT

May be something like this:

Table:

LOAD * Inline [

ID, Start, End

1, 17/04/2016, 18/06/2016

1, 18/06/2016, 20/10/2016

1, 01/11/2016, 31/12/2016

1, 02/01/2017, 03/03/2017

];

Temp:

LOAD *,

If(RowNo() > 1, If(Peek('End') = Start, 1, 0)) as Flag,

If(RowNo() > 1, If(Peek('End') = Start, Null(), Peek('End'))) as NewStart,

Start as NewEnd

Resident Table

Order By ID, Start;

Concatenate(Table)

LOAD -99 as ID,

NewStart as Start,

NewEnd as End

Resident Temp

Where Flag = 0;

DROP Table Temp;

suzel404 — Wed, 23 Mar 2016 15:26:17 GMT

Hi Sunny,

I tried with my data but I have null Mark_ID.

Can you help me ?

Thanks

sunny_talwar — Wed, 23 Mar 2016 15:51:48 GMT

I will check this at a later point today.

Best,

Sunny