lookup/applymap with condition

[#1]

Hi all,

I want to be able to retrieve a certain 'date' for some entity (vehicle for that matter). The Original data is made of 10-100's of rows per each vehicle, each row having its date & dept. I want to retrieve the earliest date for each vehicle, but that date has to come from one specific department (not the general minimal date). Is there such a functionality that can be used with either lookup or apply map, like "lookup(...) where(...)", or Mapping(vehicle, date where()..)?

Thanks in advance.

[morganaaron]

Hi Yoni,

If you simply need to know the date, you can set up a straight table chart, add Vehicle as your dimension and use:

min({<Department={'Your Department'}>}[Your Date Field])

Replacing "your department" with the name of the specific department and replacing "your date field" with the name of the date field you're using - if this is abstract you may need to wrap it in a Date() function.

[#3]

Perhaps I forgot to mention, all this is being performed in the script/load phase. This kind of expression will not help in this situation I believe.

[Gabriel]

Hi,

I think your best option is LookUp

[#5]

Hi yoni,

I attached an example that may solve your problem at script time.

Rubén.

[maxgro]

if your table v has vehicle, dept and date, I think you can join v with v grouped and filltere by the department to add a flag for the first date by vehicle

left join (v)

load

  vehicle,

  dept,

  min(date) as date,

  min(1) as flag_min_date_x_vehicle

Resident v

Where dept = 'b'

group by vehicle, dept