topic Re: Help with Peek Date function in QlikView

Help with Peek Date function

Anonymous — Mon, 28 Aug 2017 17:10:17 GMT

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.

Re: Help with Peek Date function

sunny_talwar — Mon, 28 Aug 2017 17:13:33 GMT

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

];

Re: Help with Peek Date function

Anonymous — Mon, 28 Aug 2017 17:29:38 GMT

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.

Re: Help with Peek Date function

Anonymous — Mon, 28 Aug 2017 17:32:25 GMT

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

+ sortable aggr() if needed

Re: Help with Peek Date function

Anonymous — Mon, 28 Aug 2017 17:35:15 GMT

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

sunny_talwar — Mon, 28 Aug 2017 17:37:38 GMT

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

Re: Help with Peek Date function

Anonymous — Mon, 28 Aug 2017 17:41:36 GMT

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

Re: Help with Peek Date function

Anonymous — Mon, 28 Aug 2017 17:46:41 GMT

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

Re: Help with Peek Date function

Anonymous — Mon, 28 Aug 2017 17:51:46 GMT

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)

,' | ')