topic Re: lookup/applymap with condition in QlikView

lookup/applymap with condition

Thu, 29 Jan 2015 09:04:03 GMT

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.

Re: lookup/applymap with condition

morganaaron — Thu, 29 Jan 2015 09:45:55 GMT

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.

Re: lookup/applymap with condition

Thu, 29 Jan 2015 16:12:41 GMT

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.

Re: lookup/applymap with condition

Gabriel — Thu, 29 Jan 2015 16:17:44 GMT

Hi,

I think your best option is LookUp

Re: lookup/applymap with condition

Thu, 29 Jan 2015 17:57:50 GMT

Hi yoni,

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

Rubén.

Re: lookup/applymap with condition

maxgro — Thu, 29 Jan 2015 19:55:25 GMT

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