2 Replies Latest reply: Feb 10, 2014 11:48 AM by Charles Phillips RSS

    Census date headcount

      Hi,

       

      wondered is anyone can help point me in the right direction with this,

       

      I have a set data which list all posts (jobs) in an organisation since 2008 and the post holder employed in each, within this is included two fields

       

      post start date

      post leaving date (be blank if the person is still in post)

       

      I want to be able have a selection list of the census dates (e.g. 1st April 2008,1 April 2009,1 April 2010, 1 April 2011 etc) and use this to filter the graphs/tables as to who was employed on the census date selected.

       

      the logic of filtering who is employed from these two dates would be:

      if 'post start date' is less than or equal to [census date] and 'post leaving date' is more than or equal to [census date] or is null

       

      Would this be something feasible from an expression in the front end or would something need to be generated in the load?

       

      Any ideas appreciated.

       

      Thanks

        • Re: Census date headcount
          Nicole Smith

          You'll need to use interval match in the load script, something like this:

          Jobs:

          LOAD * INLINE [

          Employee,Start Date,End Date

          John Smith,3/31/2008,4/2/2010

          Jane Smith,4/1/2008,3/31/2009

          Joe Smith,4/15/2008,

          ];

           

          CensusDate:

          LOAD * INLINE [

          Census Date

          4/1/2008

          4/1/2009

          4/1/2010

          4/1/2011

          ];

           

          IntervalMatch:

          IntervalMatch([Census Date])

          LOAD [Start Date], [End Date]

          RESIDENT Jobs;

           

          Do not try to get rid of the synthetic key that is created by the interval match--it is perfectly fine.

           

          I have attached a working example.