Count IF on another table

[MikeH1983]

Hi all. I have a tricky question, hopefully someone out there can help me.

I am trying to move a daily Headcount calculation into the Script because it is too slow in the front end.

My goal is to take my table UnitHeadcountTemp, which is every [HeadcountDate] * every [Personnel Area], and calculate the headcount for each row's unit and date, with a couple of other conditions. 

I have joined everal tables into a single personHeadcountTemp table. Now I want to take each row of UnitHeadcountTemp, and count how many rows of personHeadcountTemp meet the right conditions. This can go in a new table UnitHeadcountTOTALS. 

Here is my script so far:

UnitHeadcountTemp:      <- this works fine
    load
        MasterDate as HeadcountDate
        Resident [Master Calendar];
Join(UnitHeadcountTemp)
Load
 [Personnel Area]
    Resident [Position History];

personHeadcountTemp:      <- this works fine
 Load [Personnel Number]
 Resident People;

Left Join (personHeadcountTemp)
 Load
  [Personnel Number],
     [Employment Status],
     [Action Start Date],
     [Action End Date]
     Resident Actions;

Left Join (personHeadcountTemp)
 Load
  [Personnel Number],
     [Position Start Date],
     [Position End Date],
     [Personnel Area]
     Resident [Position History];

UnitHeadcountTOTALS:        
 Load
    HeadcountDate,
    [Personnel Area],
    count( distinct   [in personHeadcountTemp]   <- I cant figure out the Count() function here!
  IF ( [Employment Status] <> 'Withdrawn'   
      and [Action Start Date] <= HeadcountDate
      and [Action End Date] >= HeadcountDate
      and [Position Start Date] <= HeadcountDate
      and [Position End Date] >= HeadcountDate
       ,  [Personnel Number])
     ) as Headcount    
    Resident UnitHeadcountTemp;
    

So I cant figure out how to point the Count function here at^the table personHeadcountTemp. The date range is 3 years so I am wary about cross joining every date into the personHeadcountTemp table and increasing its size x1000. Any ideas?

[Anil_Babu_Samineni]

Perhaps this?

UnitHeadcountTOTALS:        
 Load
    HeadcountDate,
    [Personnel Area],
    count( distinct   [in personHeadcountTemp]) as  [in personHeadcountTemp],
  IF ( [Employment Status] <> 'Withdrawn'   
      and [Action Start Date] <= HeadcountDate
      and [Action End Date] >= HeadcountDate
      and [Position Start Date] <= HeadcountDate
      and [Position End Date] >= HeadcountDate
       ,  [Personnel Number])
     ) as Headcount    
    Resident UnitHeadcountTemp Group By HeadcountDate, [Personnel Area],
  [Employment Status],   [Action Start Date] , HeadcountDate, [Action End Date] ,[Position Start Date] ,

[Position End Date],  [Personnel Number] ;

[MikeH1983]

Hi Anil
Thanks so far. This solution is not working for me though. Pasting it as written gives me an error due to an extra closing bracket before 'as Headcount'. Removing this causes an error saying Employment Status is not found.
Can you please talk me through the steps of what this code is doing? I haven't come across this use of 'in' before.
Mike

[marcus_sommer]

Usually it's much easier to resolve the start/end dates with a real date which is associated with a master-calendar. This could be done with an IntervalMatch. Afterwards you could use rather simple expressions like:

count(Employee) or count(distinct Employee) or maybe something like: sum(EmployeeFlag)

- Marcus

[MikeH1983 · Accepted solution]

Hi all

Thank for your help. I solved this by looping through dates, and on each loop loading to the new table a count of the other table, with the relevant conditions. This works now that the Count is only referring to one table. However it is very slow, so I am putting it aside for now. 

Here is the working code FYI:

personHeadcountTemp:
 Load [Personnel Number]
 Resident People;

Left Join (personHeadcountTemp)
 Load
  [Personnel Number],
     [Employment Status] as PTEmpStat,
     [Action Start Date] as PTActStart,
     [Action End Date] as PTActEnd
     Resident Actions;

Left Join (personHeadcountTemp)
 Load
  [Personnel Number],
     [Position Start Date] as PTPosStart,
     [Position End Date] as PTPosEnd,
     [Personnel Area] as PTArea,
     [Manager Name] as PTManager
     Resident [Position History];
    
let counter = NoOfRows('Master Calendar');
 

FOR i=0 to $(counter)-1 
    LET vDate = Peek('MasterDate',$(i),'Master Calendar');
     // TRACE $(vDate);
   
    UnitHeadcount:
        load
        '$(vDate)' as HeadcountDate,
        PTArea as [Personnel Area],
        count( distinct
   if ( PTEmpStat <> 'Withdrawn'
       and PTActStart <= '$(vDate)'
       and PTActEnd >= '$(vDate)'
       and PTPosStart <= '$(vDate)'
       and PTPosEnd >= '$(vDate)'              
           and PTHoursStart <= '$(vDate)'
       and PTHoursEnd >= '$(vDate)'              
       ,  [Personnel Number]) ) as Headcount
        Resident personHeadcountTemp
        Group by PTArea;

NEXT