Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

alexandermllr
Contributor

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

Tags (3)
1 Solution

Accepted Solutions

Re: Qlikview - Fill data only between

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;

11 Replies
Chanty4u
Esteemed Contributor III

Re: Qlikview - Fill data only between

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
Contributor

Re: Qlikview - Fill data only between

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

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
Contributor

Re: Qlikview - Fill data only between

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.

Re: Qlikview - Fill data only between

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

Highlighted
alexandermllr
Contributor

Re: Qlikview - Fill data only between

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

Re: Qlikview - Fill data only between

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
Contributor

Re: Qlikview - Fill data only between

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

Re: Qlikview - Fill data only between

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;