Skip to main content
Announcements
Customer Spotlight: Discover what’s possible with embedded analytics Oct. 16 at 10:00 AM ET: REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
alexandermllr
Creator
Creator

Qlikview - Fill data only between

Hello,

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

KeyDate

Field 2

123401.01.2018
123402.01.2018Data 2
123403.01.2018
123404.01.2018
123405.01.2018Data 2
123406.01.2018

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

KeyDate

Field 2

123401.01.2018
123402.01.2018Data 2
123403.01.2018Data 2
123404.01.2018Data 2
123405.01.2018Data 2
123406.01.2018

With my script everything is filled with the last value.

Can someone help me?

Thanks

1 Solution

Accepted Solutions
sunny_talwar

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;

View solution in original post

11 Replies
Chanty4u
MVP
MVP

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
Creator
Creator
Author

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

KeyDate

Field 2

123401.01.2018
123402.01.2018Data 2
123403.01.2018
123404.01.2018
123405.01.2018Data 2
123406.01.2018
123407.01.2018
123408.01.2018Data 2
123409.01.2018
123410.01.2018

What would be the expected output now?

alexandermllr
Creator
Creator
Author

KeyDate

Field 2

123401.01.2018
123402.01.2018Data 2
123403.01.2018Data 2
123404.01.2018Data 2
123405.01.2018Data 2
123406.01.2018Data 2
123407.01.2018Data 2
123408.01.2018Data 2
123409.01.2018
123410.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
Creator
Creator
Author

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

KeyDate

Field 2

123401.01.2018
123402.01.2018Data 2
123403.01.2018
123404.01.2018
123405.01.2018Data 2
123406.01.2018
123407.01.2018
123408.01.2018Data 2
123409.01.2018
123410.01.2018
123501.01.2018
123502.01.2018Data 2
123503.01.2018
123504.01.2018
123505.01.2018Data 2
123506.01.2018
123507.01.2018
123508.01.2018Data 2
123509.01.2018
123510.01.2018
alexandermllr
Creator
Creator
Author

Oh ok sorry.

KeyDate

Field 2

123401.01.2018
123402.01.2018Data 2
123403.01.2018Data 2
123404.01.2018Data 2
123405.01.2018Data 2
123406.01.2018Data 2
123407.01.2018Data 2
123408.01.2018Data 2
123409.01.2018
123410.01.2018
123501.01.2018
123502.01.2018Data 2
123503.01.2018Data 2
123504.01.2018Data 2
123505.01.2018Data 2
123506.01.2018Data 2
123507.01.2018Data 2
123508.01.2018Data 2
123509.01.2018
123510.01.2018
sunny_talwar

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;