topic Re: Peek or macros? in QlikView

Peek or macros?

Tue, 24 Mar 2015 12:24:53 GMT

Hello!

Guys please help me solve the problem,

I have a table:

I need to leave the row with the latest month before the value of the Bench field appears. Like I have in June-15 '1' in DataAccount and zero in Benh, in Jul-15 I dont have any data in DataAccount, but I have '1' in bench. So I need Qlikview to leave me the row with Jun-15 data.

So the table should look like:

Please Help!

Re: Peek or macros?

Gabriel — Tue, 24 Mar 2015 12:37:12 GMT

Hi,

Try this in your load

Final:

Load

,if(previous(Employee)=Employee AND Bench <> 0, DataAccount) as Flag

RESIDENT Temp_Table;

Drop Table Temp_Table;

Re: Peek or macros?

hic — Tue, 24 Mar 2015 20:40:06 GMT

An alternative method, where you always get the last record with Bench=0 could be the following

//=========== Load table

tmp:

Load * From Source;

//=========== Sort table backwards, and run one more pass

Data:

Load *,

If( Bench=0 and Peek(Bench)=1, True(), False()) as Flag

Resident tmp

Order By Employee, Period Desc;

Drop Table tmp;

HIC