Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
This load script will do exactly what you want:
EMPTABLE:
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);
SUMMARYTABLE:
LOAD
PositionID,
SubField( Concat(Name,'|',RecNo()) , '|' , 1 ) AS CurrentEmp,
SubField( Concat(Name,'|',RecNo()) , '|' , 2 ) AS ReplacementEmp
RESIDENT
EMPTABLE
GROUP BY
PositionID;
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.
Thanks,
Maria
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?
AllData:
LOAD
...
PositionID,
"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...
MonthData:
load
*,
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;
FlagData:
load
*,
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;
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.
Thanks,
Maria