lookup or peek?

[#1]

Hello,

Could someone help me, please? I've got a table like

salary month	Object_ID	TOP_manager
01.01.2014	25	Thompson Eric
01.01.2014	36	Grundalf Mark
01.01.2014	48	Stowny Oliver
01.01.2014	52	Clinton Scott
01.02.2014	25	Thompson Eric
01.02.2014	36	Grundalf Mark
01.02.2014	48	Stowny Oliver
01.02.2014	52	Newman Peter
01.03.2014	25	Thompson Eric
01.03.2014	36	Tensley Tom
01.03.2014	48	Stowny Oliver
01.03.2014	52	Newman Peter
01.04.2014	25	Thompson Eric
01.04.2014	36	Tensley Tom
01.04.2014	48	Clarksson Edward
01.04.2014	52	Newman Peter

From this data I need to get the exsperience of each top, to be able to use if further in charts (the quality of work in the dependent of the exsperience). I've tried to make a temp table, to load and sort by TOP_manager, see if it's the the first entry of top in chart (top_manager<>previos(top_manager)),put the name of first month in a special added field then count months, but it doesn't help// tried to use lookup, peek.... just doesn't work right =(

help me pleaas...

thank you in advance

experience

[Anonymous]

Alexa,

Could you please attach an image of the output expected?

Marc.

[ychaitanya]

Hi Alexa

Please explain clearly and attach sample output and if possible your application.

Thanks

Chaitanya

[#4]

Mark, Hi! thank for the response)

I expect to see

salary month	Object_ID	TOP_manager	Experience
01.01.2014	25	Thompson Eric	1
01.01.2014	36	Grundalf Mark	1
01.01.2014	48	Stowny Oliver	1
01.01.2014	52	Clinton Scott	1
01.02.2014	25	Thompson Eric	2
01.02.2014	36	Grundalf Mark	2
01.02.2014	48	Stowny Oliver	2
01.02.2014	52	Newman Peter	1
01.03.2014	25	Thompson Eric	3
01.03.2014	36	Tensley Tom	1
01.03.2014	48	Stowny Oliver	3
01.03.2014	52	Newman Peter	2
01.04.2014	25	Thompson Eric	4
01.04.2014	36	Tensley Tom	2
01.04.2014	48	Clarksson Edward	1
01.04.2014	52	Newman Peter	3

OR

salary month	Object_ID	TOP_manager	Start
01.01.2014	25	Thompson Eric	01.01.2014
	01.01.2014	36	Grundalf Mark	01.01.2014
01.01.2014	48	Stowny Oliver	01.01.2014
01.01.2014	52	Clinton Scott	01.01.2014
01.02.2014	25	Thompson Eric	01.01.2014
01.02.2014	36	Grundalf Mark	01.01.2014
01.02.2014	48	Stowny Oliver	01.01.2014
01.02.2014	52	Newman Peter	01.02.2014
01.03.2014	25	Thompson Eric	01.01.2014
01.03.2014	36	Tensley Tom	01.03.2014
01.03.2014	48	Stowny Oliver	01.01.2014
01.03.2014	52	Newman Peter	01.02.2014
01.04.2014	25	Thompson Eric	01.01.2014
01.04.2014	36	Tensley Tom	01.03.2014
01.04.2014	48	Clarksson Edward	01.04.2014
01.04.2014	52	Newman Peter	01.02.2014

i'M not sure, what will be better to make my app work faster (there are few thousands of objects and TOPs change really often....)

[#5]

Hi, Chaitanya!!

Thanks for responce! just posted

[jagan · Accepted solution]

OR

If you want Start then use below script

Data:

LOAD

*

FROM DataSource;

LEFT JOIN (Data)

LOAD

TOP_manager,

Date(Min([salary month])) AS Start

RESIDENT Data

GROUP BY TOP_manager;

Hope this helps you.

Regards,

Jagan.

[#7]

Thank you very much!! It's just what I need!!