Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
star_limit
Contributor II

Help with Peek Date function

Hello,

I have the below data loaded as a sample that imitates my real scenario.

LOAD * Inline [

EmployeeID,     Type,    Date

1001,            2,        1/1/2013               

1001,            9,        1/1/2014

1001,            1,        2/7/2014

1001,            2,        1/1/2017

2001,            9,        1/5/2013

2001,            3,        11/1/2017

3001,            2,        1/10/2017

];

With EmployeeID and Date as dimensions and Type as an expression, I see the below structure.

Below is my expected result. I need the date next after Type 9. Is it possible to get it through an expression.

For instance, IF(EmployeeID=EmployeeID and Type = 9, peek(Date),'')

EmployeeIDDate
10012/7/2014
200111/1/2017
3001Blank

Thank you.

Tags (1)
1 Solution

Accepted Solutions

Re: Help with Peek Date function

May be this

LOAD *,

If(EmployeeID = Previous(EmployeeID) and Previous(Type) = 9, Date) as SpecficDate;

LOAD * Inline [

EmployeeID,    Type,    Date

1001,            2,        1/1/2013             

1001,            9,        1/1/2014

1001,            1,        2/7/2014

1001,            2,        1/1/2017

2001,            9,        1/5/2013

2001,            3,        11/1/2017

3001,            2,        1/10/2017

];

Capture.PNG

8 Replies

Re: Help with Peek Date function

May be this

LOAD *,

If(EmployeeID = Previous(EmployeeID) and Previous(Type) = 9, Date) as SpecficDate;

LOAD * Inline [

EmployeeID,    Type,    Date

1001,            2,        1/1/2013             

1001,            9,        1/1/2014

1001,            1,        2/7/2014

1001,            2,        1/1/2017

2001,            9,        1/5/2013

2001,            3,        11/1/2017

3001,            2,        1/10/2017

];

Capture.PNG

star_limit
Contributor II

Re: Help with Peek Date function

You are a true QlikView Rockstar Sunny. Thanks so much. By the way, it won't be possible to do the IF statement in an expression, right? I guess Previous and Peek will only work in the script. Your solutions works for me though.

robin_hausdoerfer
Valued Contributor III

Re: Help with Peek Date function

then you have to work with inter record functions like above() or below()

+ sortable aggr() if needed

star_limit
Contributor II

Re: Help with Peek Date function

Oh! That will be just too complicated then. Sunny's solution is simple and easy to use compared to that. Thank you for the idea.

Re: Help with Peek Date function

Above below is not too difficult and it seems that the data is already sorted so may be you can get away with sortable aggr() function... but it will all depend on what you need

robin_hausdoerfer
Valued Contributor III

Re: Help with Peek Date function

it's not really complicated, it's just a different approach

star_limit
Contributor II

Re: Help with Peek Date function

Thanks Sunny and Robin. You guys are amazing. I learn new things every time I come here.

robin_hausdoerfer
Valued Contributor III

Re: Help with Peek Date function

my suggestion will only work, if there's exactly 1 distinct above() value within EmployeeID dimension with Type ='9'

if there are multiple hits, you must aggregate

concat(aggr(
if(Type=9,Above(total Date, -1))

,
EmployeeID,Type,Date)

,' | ')