Qlikview - Fill data only between

[alexandermllr]

Hello,

I need to fill the data, only when it's between the same data.

Key	Date	Field 2
1234	01.01.2018
1234	02.01.2018	Data 2
1234	03.01.2018
1234	04.01.2018
1234	05.01.2018	Data 2
1234	06.01.2018

Final Table should be this, in order by Key, Date

Key	Date	Field 2
1234	01.01.2018
1234	02.01.2018	Data 2
1234	03.01.2018	Data 2
1234	04.01.2018	Data 2
1234	05.01.2018	Data 2
1234	06.01.2018

With my script everything is filled with the last value.

Can someone help me?

Thanks

[Chanty4u]

may be this

LOAD

if(len(trim(Field))=0, peek(Field), Field) as Result,  // len(trim(<field>))=0 is the best way to catch NULL and empty string

Value1,

Value2

FROM...

[alexandermllr]

With that statment it fills up everything.

I always have a start and a end, only between that it should fill up the data.

[sunny_talwar]

What if you had few more rows like this

Key	Date	Field 2
1234	01.01.2018
1234	02.01.2018	Data 2
1234	03.01.2018
1234	04.01.2018
1234	05.01.2018	Data 2
1234	06.01.2018
1234	07.01.2018
1234	08.01.2018	Data 2
1234	09.01.2018
1234	10.01.2018

What would be the expected output now?

[alexandermllr]

Key	Date	Field 2
1234	01.01.2018
1234	02.01.2018	Data 2
1234	03.01.2018	Data 2
1234	04.01.2018	Data 2
1234	05.01.2018	Data 2
1234	06.01.2018	Data 2
1234	07.01.2018	Data 2
1234	08.01.2018	Data 2
1234	09.01.2018
1234	10.01.2018

I always a "start" and "end" of each data, between that it should fill the data.

[sunny_talwar]

And this is by Key... for each Key... you would want to use the same data between the start and end date?

[alexandermllr]

This could happen multiple times in 1 Key, but always in order by the key and date.

[sunny_talwar]

No, what I meant was what would be the output for this

Key	Date	Field 2
1234	01.01.2018
1234	02.01.2018	Data 2
1234	03.01.2018
1234	04.01.2018
1234	05.01.2018	Data 2
1234	06.01.2018
1234	07.01.2018
1234	08.01.2018	Data 2
1234	09.01.2018
1234	10.01.2018

1235	01.01.2018
1235	02.01.2018	Data 2
1235	03.01.2018
1235	04.01.2018
1235	05.01.2018	Data 2
1235	06.01.2018
1235	07.01.2018
1235	08.01.2018	Data 2
1235	09.01.2018
1235	10.01.2018

[alexandermllr]

Oh ok sorry.

Key	Date	Field 2
1234	01.01.2018
1234	02.01.2018	Data 2
1234	03.01.2018	Data 2
1234	04.01.2018	Data 2
1234	05.01.2018	Data 2
1234	06.01.2018	Data 2
1234	07.01.2018	Data 2
1234	08.01.2018	Data 2
1234	09.01.2018
1234	10.01.2018

1235	01.01.2018
1235	02.01.2018	Data 2
1235	03.01.2018	Data 2
1235	04.01.2018	Data 2
1235	05.01.2018	Data 2
1235	06.01.2018	Data 2
1235	07.01.2018	Data 2
1235	08.01.2018	Data 2
1235	09.01.2018
1235	10.01.2018

[sunny_talwar · Accepted solution]

Try this

Table:

LOAD * INLINE [

    Key, Date, Field 2

    1234, 01.01.2018,

    1234, 02.01.2018, Data 2

    1234, 03.01.2018,

    1234, 04.01.2018,

    1234, 05.01.2018, Data 2

    1234, 06.01.2018,

    1234, 07.01.2018,

    1234, 08.01.2018, Data 2

    1234, 09.01.2018,

    1234, 10.01.2018,

    1235, 01.01.2018,

    1235, 02.01.2018, Data 2

    1235, 03.01.2018,

    1235, 04.01.2018,

    1235, 05.01.2018, Data 2

    1235, 06.01.2018,

    1235, 07.01.2018,

    1235, 08.01.2018, Data 2

    1235, 09.01.2018,

    1235, 10.01.2018

];

Left Join (Table)

LOAD Key,

Max(Date) as MaxDate,

Min(Date) as MinDate

Resident Table

Where Len(Trim([Field 2])) > 0

Group By Key;

FinalTable:

LOAD Key,

Date,

If(Date >= MinDate and Date <= MaxDate, If(Len(Trim([Field 2])) = 0, Peek('Field 2'), [Field 2])) as [Field 2]

Resident Table

Order By Key, Date;

DROP Table Table;