Help with Peek Date function

[Anonymous]

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),'')

EmployeeID	Date
1001	2/7/2014
2001	11/1/2017
3001	Blank

Thank you.

[sunny_talwar · Accepted solution]

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

];

[Anonymous]

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.

[Anonymous]

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

+ sortable aggr() if needed

[Anonymous]

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.

[sunny_talwar]

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

[Anonymous]

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

[Anonymous]

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

[Anonymous]

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)

,' | ')