Leaving the privious record according to the condition

[#1]

Hello,guys!

Here is my new challenge. I have a table as follows:

Employee	Account	Period	DataAccount	Bench
Mark Spencer	Telecom	Dec-14	1,00
Mark Spencer	Telecom	Jan-15	0,80	0
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		Jul-15		1
Mark Spencer		Aug-15		1
Mark Spencer		Sep-15		1

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.

Any suggestions how can I do it? May be I need  macro or something, but I dont know how to write macros in qlikview.

Please help!

[#2]

Hi

Load *

from source where not wildmatch(Period,'Jun-15');

[#3]

Hi Harshal,

it cant be always Jun15, this is a part of table and the full table contains information of a big number of employees. Thus every employee has his own "Latest information before Bench values", so it cant be always Jun15. I need to check these rows for each employee.

[#4]

Guuuys, any ideas??? Really need your help!

[maxgro]

I added some test data

source:

load *, date(Date#(Period,'MMM-DD')) as NewPeriod inline [

Employee, Account, Period, DataAccount, Bench

Mark Spencer, Telecom, Dec-14, 1.00,

Mark Spencer, Telecom, Jan-15, 0.80, 0

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, , Jul-15 ,, 1

Mark Spencer, , Aug-15 ,, 1

Mark Spencer, , Sep-15 ,, 1

Mark2 , Telecom, Dec-14, 1.00,

Mark2 , Telecom, Jan-15, 0.80, 0

Mark2 , Telecom, Feb-15, 1.00, 0

Mark2 , Telecom, Mar-15, 1.00, 0

Mark2 , Telecom, Apr-15, 1.00, 0

Mark2 , Telecom, May-15, 1.00, 0

Mark2 , Telecom, Jun-15, 1.00, 0

Mark2 , , Jul-15 ,, 1

Mark2 , , Aug-15 ,, 1

Mark2 , , Sep-15 ,, 1

];

final:

NoConcatenate load

  *,

  if(Employee= Peek(Employee) and peek(Bench)=1 and Bench=0

  ,

  1, '') as Flag

Resident

  source

order by Employee, NewPeriod desc;

DROP Table source;

[#6]

Hi, Massimo!

The idea is great, but it doesnt work properly in my file. It puts '1' in front of different dates before bench and i need it to put this flag just before the bench begins

[#7]

Massimo,

I figured out what the problem is. Qlikview doesnt convert all the numeric values that i have in numbers

[#8]

I've fixed the numbers issue, but it still isnt working properly... Any ideas?

[maxgro]

post some data, a .qvw, thanks