Re: set analysis in dimension or join - Qlik Community

Anonymous · ‎2017-03-20

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?

petter · ‎2017-03-20

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;

Anonymous · ‎2017-03-21

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

petter · ‎2017-03-21

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?

Anonymous · ‎2017-03-21

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;

Anonymous · ‎2017-03-22

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