topic Re: Peek function doesnt work properly in QlikView

Peek function doesnt work properly

Tue, 24 Mar 2015 14:53:03 GMT

Hi!

I have a table with Employees, Account , DataAccount,Bench

This is a part of my table:

Employee	Account	Period	DataAccount	Bench
Mark Spencer	Telecom	Feb-15	1.00	0
Mark Spencer	Telecom	Mar-15	1.00	0
Mark Spencer	Telecom	Apr-15	1.00	0
Mark Spencer	Telecom	May-15	1.00	0
Mark Spencer	Telecom	Jun-15	1.00	0
Mark Spencer	Telecom	Jul-15	1.00	1
Mark Spencer	Telecom	Aug-15	1.00	1
Mark Spencer	Telecom	Sep-15	1.00	1
Mark Spencer	Telecom	Oct-15	1.00	1
Mark Spencer	Telecom	Nov-15	1.00	1
Mark Spencer	Telecom	Dec-15	1.00	1

I need to find the last row before the values in Bench appear like this:

Employee	Account	Period	DataAccount	Bench	Flag
Mark Spencer	Telecom	Feb-15	1.00	0
Mark Spencer	Telecom	Mar-15	1.00	0
Mark Spencer	Telecom	Apr-15	1.00	0
Mark Spencer	Telecom	May-15	1.00	0
Mark Spencer	Telecom	Jun-15	1.00	0	1
Mark Spencer	Telecom	Jul-15	1.00	1
Mark Spencer	Telecom	Aug-15	1.00	1
Mark Spencer	Telecom	Sep-15	1.00	1
Mark Spencer	Telecom	Oct-15	1.00	1
Mark Spencer	Telecom	Nov-15	1.00	1
Mark Spencer	Telecom	Dec-15	1.00	1

Re: Peek function doesnt work properly

ramoncova06 — Tue, 24 Mar 2015 14:58:45 GMT

this has been working for me

if(Employee = Previous(Employee) and Account = Previous(Account) and DataAccount = Previous(Account),

if( Previous(Bench)= Bench, peek('Flag') 1),0) as Flag

Re: Peek function doesnt work properly

Tue, 24 Mar 2015 15:03:02 GMT

Nope,

it shows me an Error.

peek ('Flag') expression isn't working

Re: Peek function doesnt work properly

jolivares — Tue, 24 Mar 2015 15:15:44 GMT

In this case you have to explore the next record to see if the bench has change, but this is no possible. In this case i do the following:

T1:

Load.... " your table"

Then...

Join (T1)

Load Employee, Account, Max(Period) as Period, DataAccount, Bench, 1 as Flag

Resident T1

Where Bench = 0

Group By Employee, Account, DataAccount, Bench;

Check it out...

Re: Peek function doesnt work properly

Tue, 24 Mar 2015 15:21:32 GMT

no, it doesnt give me the desired result. I want it to show me exactly like that:

Employee	Account	Period	DataAccount	Bench	Flag
Mark Spencer	Telecom	Feb-15	1.00	0
Mark Spencer	Telecom	Mar-15	1.00	0
Mark Spencer	Telecom	Apr-15	1.00	0
Mark Spencer	Telecom	May-15	1.00	0
Mark Spencer	Telecom	Jun-15	1.00	0	1
Mark Spencer	Telecom	Jul-15	1.00	1
Mark Spencer	Telecom	Aug-15	1.00	1
Mark Spencer	Telecom	Sep-15	1.00	1
Mark Spencer	Telecom	Oct-15	1.00	1
Mark Spencer	Telecom	Nov-15	1.00	1
Mark Spencer	Telecom	Dec-15	1.00	1

Re: Peek function doesnt work properly

ramoncova06 — Tue, 24 Mar 2015 15:25:29 GMT

was missing a comma

temp:

LOAD Employee,

Account,

Period,

DataAccount,

Bench

FROM

[Test.xlsx]

(ooxml, embedded labels, table is Sheet1);

temp:

load

Employee,

Account,

Period,

DataAccount,

Bench,

if(Employee = Previous(Employee) and Account = Previous(Account) and DataAccount = Previous(DataAccount),

if(Previous(Bench)= Bench, peek('Flag'), 1),0) as Flag

resident temp

order by Employee,Period,Account ;

drop table temp;

Re: Peek function doesnt work properly

ramoncova06 — Tue, 24 Mar 2015 15:26:50 GMT

if you only want it to show for when the change happen switch it to this

if(Employee = Previous(Employee) and Account = Previous(Account) and DataAccount = Previous(DataAccount),

if(Previous(Bench)= Bench, null(), 1)) as Flag

Re: Peek function doesnt work properly

Tue, 24 Mar 2015 15:33:42 GMT

I want to mark the row just before the change happens

Re: Peek function doesnt work properly

prieper — Tue, 24 Mar 2015 15:36:41 GMT

This should work:

Data_raw:

LOAD *, DATE(DATE#(Period, 'MMM-YY')) AS Date INLINE [

Employee, Account, Period, DataAccount, Bench

Mark Spencer, Telecom, Feb-15, 1.00, 0

Mark Spencer, Telecom, Mar-15, 1.00, 0

Mark Spencer, Telecom, Apr-15, 1.00, 0

Mark Spencer, Telecom, May-15, 1.00, 0

Mark Spencer, Telecom, Jun-15, 1.00, 0

Mark Spencer, Telecom, Jul-15, 1.00, 1

Mark Spencer, Telecom, Aug-15, 1.00, 1

Mark Spencer, Telecom, Sep-15, 1.00, 1

Mark Spencer, Telecom, Oct-15, 1.00, 1

Mark Spencer, Telecom, Nov-15, 1.00, 1

Mark Spencer, Telecom, Dec-15, 1.00, 1

];

Data:

LOAD

IF(Employee = PEEK(Employee) AND Bench <> PEEK(Bench), 1) AS Flag

RESIDENT

Data_raw

ORDER BY

Employee ASC,

Date DESC;

DROP TABLE Data_raw;

HTH Peter

Re: Peek function doesnt work properly

jolivares — Tue, 24 Mar 2015 15:41:19 GMT

I think you can use the last solution if you do this:

...

Order by Employee, Acouunt, Bench desc, Period desc;

Re: Peek function doesnt work properly

Tue, 24 Mar 2015 15:51:10 GMT

This works well, but I found one bug here, in one case it showed me this:

Employee	Account	Period	DataAccount	Bench	Flag
Pavel Kovalski	International	Sep-14	0.18	0	1
Pavel Kovalski	Telecom	Feb-14	1.00	0
Pavel Kovalski	Telecom	Mar-14	1.00	0
Pavel Kovalski	Telecom	Apr-14	1.00	0
Pavel Kovalski	Telecom	May-14	1.00	0
Pavel Kovalski	Telecom	Jun-14	0.47	0
Pavel Kovalski		Jan-15		1
Pavel Kovalski		Feb-15		1
Pavel Kovalski		Mar-15		1
Pavel Kovalski		Apr-15		1

I want it to put 1 in front of Jun-14

Re: Peek function doesnt work properly

Tue, 24 Mar 2015 15:54:20 GMT

Sorry, it's not a bug! This works perfectly great!

Thanks!

Re: Peek function doesnt work properly

jopmoekotte — Tue, 24 Mar 2015 16:10:45 GMT

I think Peter already solved it for you, however I personally prefer using keyfields (combining multiple fields) to avoid multiple IF AND statements. (however, temporarily, bloating your table).

my suggestion:

Data_raw:

LOAD *,

Date(Date#(Period, 'MMM-YY')) as Date,

Employee &'_'& Account as Key_Peek

;

LOAD * INLINE [

Employee, Account, Period, DataAccount, Bench

Mark Spencer, Telecom, Feb-15, 1.00, 0

Mark Spencer, Telecom, Mar-15, 1.00, 0

Mark Spencer, Telecom, Apr-15, 1.00, 0

Mark Spencer, Telecom, May-15, 1.00, 0

Mark Spencer, Telecom, Jun-15, 1.00, 0

Mark Spencer, Telecom, Jul-15, 1.00, 1

Mark Spencer, Telecom, Aug-15, 1.00, 1

Mark Spencer, Telecom, Sep-15, 1.00, 1

Mark Spencer, Telecom, Oct-15, 1.00, 1

Mark Spencer, Telecom, Nov-15, 1.00, 1

Mark Spencer, Telecom, Dec-15, 1.00, 1

];

[Data]:

LOAD *,

if(Key_Peek=Peek(Key_Peek,-1),if(Bench<>Peek(Bench,-1),1,null()),null()) as Flag

RESIDENT Data_raw ORDER BY Key_Peek, Date DESC;

drop table Data_raw;