5 Replies Latest reply: Mar 22, 2017 2:55 PM by Maria Harmening RSS

    set analysis in dimension or join

    Maria Harmening

      hi.  I have data that looks similar to this..


      PositionID          Name            DateRotation        DateIncoming            DepartDays           IncomingDays

      clerk123            Maria             3/31/2017               prior to 2015                14                          doesn't matter

      clerk123            John              4/62020                  4/6/2017                     a long time             20

      janitor123          Chris             7/31/2017               prior to 2015                130                         doesn't matter

      janitor123          Harry             7/31/2020               7/1/2017                     a long time             100

      super123           Nina              4/1/2017                 prior to 2015                21                           doesn't matter

      super123           Dave              12/31/2019            3/31/2017                   a long time              11

      manager123      Vern             3/31/2017               prior                              14                              doesn't matter


      this is how I want to display it..

      Position           CurrentEmp           ReplacementEmp

      clerk123           Maria                     John

      jan123              Chris                      Harry

      super123          Nina                       Dave

      manager123     Vern                     


      so, the  positionID is the link and the dates would determine current v replacement.  this will allow leadership to see the gaps in personnel.  ie, everything is staffed except manager123 has no replacement.    it seems like it should be simple.  can't I just grab the ones current in one set, then grab the replacements in another set and union them on the positionID?   can I do this in a dimension?  or do I have to do it within the data load?  it seems like I shouldn't have to alter the data itself because everything is there...I just need to figure out how to manipulate within the view.  any suggestions?  what am I misunderstanding?

        • Re: set analysis in dimension or join
          Petter Skjolden

          This load script will do exactly what you want:


          LOAD * INLINE [
          PositionID Name DateRotation        DateIncoming  DepartDays IncomingDays
          clerk123  Maria   3/31/2017     prior to 2015      14      doesn't matter
          clerk123  John    4/62020        4/6/2017 a long time   20
          janitor123 Chris   7/31/2017     prior to 2015      130     doesn't matter
          janitor123 Harry   7/31/2020     7/1/2017 a long time   100
          super123 Nina    4/1/2017       prior to 2015      21       doesn't matter
          super123 Dave    12/31/2019  3/31/2017         a long time    11
          manager123      Vern   3/31/2017     prior 14 doesn't matter
          ] (delimiter is \t);
              SubField( Concat(Name,'|',RecNo()) , '|' , 1 ) AS CurrentEmp,
              SubField( Concat(Name,'|',RecNo()) , '|' , 2 ) AS ReplacementEmp    
          GROUP BY
            • Re: set analysis in dimension or join
              Maria Harmening

              thank you..  so, my data doesn't look exactly like that...  I was just trying to get an idea of how I can do this.  it looks like I need to solve the problem in the initial load and will have to create the ReplacementEmp field.  I was hoping that I wouldn't have to do that...


              so, in the initial load, should I use the group by PositionID then IncomingDate?  in your statement, you use subfield function...what is all of that doing?  you have the Name|RecNo()| with the 1 and 2.  what is that returning?  obviously it's the name values, but what does the recno() with 1 and 2 return?


              I'm trying to relate this to my data.




                • Re: set analysis in dimension or join
                  Petter Skjolden

                  The Concat works as an aggregation and put the two names together in one string with the vertical bar | as the separator. The RecNo() makes sure that the names come in the right order. That the first occuring name comes first in the string. The Subfield is to pick out the first name for CurrentTemp and the second name for the ReplacementEmp.


                  The group by is used to be able to use the aggregation and get one single line with two employees. So it should not be used in your intial load unless you only need the result table. Then you should be able to do this with a single load....


                  Do you have an example of your actual load statement to share?

              • Re: set analysis in dimension or join
                Maria Harmening





                   "Position Title",

                    "Position Begin Date",

                    "Position End date",

                     "Emp Name",

                    "Employee On Board Date",

                    "Employee Rotation Date (PRD)",




                above is the relevant data for the initial load.   I have subsequent resident loads where I create the monthly and yearly flag data for charting purposes..   here they are....  I was trying avoid sharing all of this, but it shows the data.  the issues are not as straightforward as the initial question.  I have position begin and end dates and employee begin and end dates.  I also have instances where

                1.there is an active position that has no employee in it

                2. there is an employee present but not mapped to a position

                just some of them...


                    if("Emp Name"='!', 0, 1) as EmpPresent,
                    if("PositionID"='!', 0, 1) as PositionPresent,
                Date(addmonths(today(),IterNo()-1), 'MMM-YYYY') as MonthYear,
                    YearName(addmonths(today(),IterNo()-1)) as FYYear,
                    If(Month(addmonths(today(),IterNo()-1)) >= 10, MakeDate(Year(addmonths(today(),IterNo()-1)), 10, 1 ), MakeDate(Year(addmonths(today(),IterNo()-1))-1, 10, 1)) as FiscalYearBegin,
                    If(Month(addmonths(today(),IterNo()-1)) >= 10, MakeDate(Year(addmonths(today(),IterNo()-1))+1, 9, 30 ), MakeDate(Year(addmonths(today(),IterNo()-1)), 9, 30)) as FiscalYearEnd,
                    If(Month(addmonths(today(),IterNo()-1)) <=3, MakeDate(Year(addmonths(today(),IterNo()-1)), 1, 1 ),
                    IF(Month(addmonths(today(),IterNo()-1)) <=6, MakeDate(Year(addmonths(today(),IterNo()-1)), 4, 1 ),
                    IF(Month(addmonths(today(),IterNo()-1)) <=9, MakeDate(Year(addmonths(today(),IterNo()-1)), 7, 1 ),
                    MakeDate(Year(addmonths(today(),IterNo()-1)), 10, 1 )))) as QuarterBegin,
                    If(Month(addmonths(today(),IterNo()-1)) <=3, MakeDate(Year(addmonths(today(),IterNo()-1)), 3, 31 ),
                    IF(Month(addmonths(today(),IterNo()-1)) <=6, MakeDate(Year(addmonths(today(),IterNo()-1)), 6, 30 ),
                    IF(Month(addmonths(today(),IterNo()-1)) <=9, MakeDate(Year(addmonths(today(),IterNo()-1)), 9, 30 ),
                    MakeDate(Year(addmonths(today(),IterNo()-1)), 12, 31 )))) as QuarterFinish,

                    Iterno()-1 as OrderBy,
                    '$(vToday)' as NumToday
                Resident AllData
                While Num(addmonths(Monthstart(today()),IterNo()-1)) <= '$(vEndDateNumMonthData)'
                Drop Table AllData;

                    If((EmpPresent = 1) and (PositionPresent = 0), 1, 0) as EmpUnmapped,
                    If((PositionPresent = 1) and (EmpPresent = 0), 1, 0) as PositionUnmapped,
                    (EmpPresent * (Num(Date("Emp On Board Date")) <= NumToday) * (Num(Date("Emp Rotation Date (PRD)")) >= NumToday)) as EmpFlagToday,
                    (PositionPresent * (Num(Date("Position Begin Date")) <= NumToday) * (Num(Date("Position End date")) >= NumToday)) as PositionFlagToday,
                    (EmpPresent * (Num(Date("Emp On Board Date")) <= Num(MonthEnd(MonthYear))) * (Num(Date("Emp Rotation Date (PRD)")) >= Num(MonthStart(MonthYear)))) as EmpFlagMonth,
                    (PositionPresent * (Num(Date("Position Begin Date")) <= Num(MonthEnd(MonthYear))) * (Num(Date("Position End date")) >= Num(MonthStart(MonthYear)))) as PositionFlagMonth,
                (EmpPresent * (Num(Date("Emp On Board Date")) <= Num(Date(QuarterFinish))) * (Num(Date("Emp Rotation Date (PRD)")) >= Num(Date(QuarterBegin)))) as EmpFlagQuarter,
                    (PositionPresent * (Num(Date("Position Begin Date")) <= Num(Date(QuarterFinish))) * (Num(Date("Position End date")) >= Num(Date(QuarterBegin)))) as PositionFlagQuarter,
                    (EmpPresent * (Num(Date("Emp On Board Date")) <= Num(Date(FiscalYearEnd))) * (Num(Date("Emp Rotation Date (PRD)")) >= Num(Date(FiscalYearBegin)))) as EmpFlagYear,
                    (PositionPresent * (Num(Date("Position Begin Date")) <= Num(Date(FiscalYearEnd))) * (Num(Date("Position End date")) >= Num(Date(FiscalYearBegin)))) as PositionFlagYear,
                    If(Month(MonthYear) >= 10, Month(MonthYear)-9, Month(MonthYear)+3) as FYYearMonthNum,
                    If(Month(MonthYear) >= 10, Text(MonthName(addYears(MonthYear, 1))), Text(MonthYear)) as FYDisplayYearMonth,
                    If(Month(MonthYear) >= 10, YearName(addyears(FYYear, 1)), FYYear) as FYDisplayYear,
                    Num(Date("Emp Rotation Date (PRD)")) - '$(vToday)' as DeltaDays,
                    Num(Date("Emp On Board Date")) - '$(vToday)' as IncomingDays
                Resident MonthData
                Drop Table MonthData;

                • Re: set analysis in dimension or join
                  Maria Harmening

                  I was making it much more difficult than it needed to be.  I made an incoming data table and a departing data table with a common positioned between the two.  so far so good.