topic Re: Qlikview - Fill data only between in QlikView

Qlikview - Fill data only between

alexandermllr — Wed, 27 Jun 2018 07:26:50 GMT

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

Re: Qlikview - Fill data only between

Chanty4u — Wed, 27 Jun 2018 07:33:43 GMT

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...

Re: Qlikview - Fill data only between

alexandermllr — Wed, 27 Jun 2018 07:39:53 GMT

With that statment it fills up everything.

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

Re: Qlikview - Fill data only between

sunny_talwar — Thu, 28 Jun 2018 11:37:41 GMT

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?

Re: Qlikview - Fill data only between

alexandermllr — Thu, 28 Jun 2018 11:40:08 GMT

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.

Re: Qlikview - Fill data only between

sunny_talwar — Thu, 28 Jun 2018 11:44:20 GMT

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

Re: Qlikview - Fill data only between

alexandermllr — Thu, 28 Jun 2018 11:49:00 GMT

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

Re: Qlikview - Fill data only between

sunny_talwar — Thu, 28 Jun 2018 12:02:25 GMT

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

Re: Qlikview - Fill data only between

alexandermllr — Thu, 28 Jun 2018 12:04:27 GMT

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

Re: Qlikview - Fill data only between

sunny_talwar — Thu, 28 Jun 2018 12:10:47 GMT

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;

Re: Qlikview - Fill data only between

alexandermllr — Thu, 28 Jun 2018 13:13:21 GMT

I works, but not always.

I forgot something, 1 Key can have more different data:

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

I would need the table to be like this:

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

Re: Qlikview - Fill data only between

sunny_talwar — Thu, 28 Jun 2018 13:35:39 GMT

Try this

Table:

LOAD * INLINE [

Key, Date, Field 2

1234, 01.01.2018,

1234, 02.01.2018, Data 1

1234, 03.01.2018,

1234, 04.01.2018,

1234, 05.01.2018, Data 1

1234, 06.01.2018,

1234, 07.01.2018, Data 2

1234, 08.01.2018,

1234, 09.01.2018, Data 2

1234, 10.01.2018

];

TempTable:

NoConcatenate

LOAD Key,

Date,

[Field 2],

If(Len(Trim([Field 2])) = 0, Peek('Field 2 New'), [Field 2]) as [Field 2 New]

Resident Table

Order By Key, Date;

DROP Table Table;

Left Join (TempTable)

LOAD Key,

[Field 2] as [Field 2 New],

Max(Date) as MaxDate,

Min(Date) as MinDate

Resident TempTable

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

Group By Key, [Field 2];

FinalTable:

LOAD Key,

Date,

If(Date >= MinDate and Date <= MaxDate, [Field 2 New]) as [Field 2]

Resident TempTable

Order By Key, Date;

DROP Table TempTable;