Peek or macros?

[#1]

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!

[Gabriel]

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;

[hic]

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