hi. here is more of a subset of the data from the database...this is not the real data. it's a very simplistic version that helps you see the gist of what I'm doing.
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
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?
Find the attachment.
I'm not sure if that's what you want, you can test it on large scale (your data)....
Remove Order by Position from the script, it's pretty much useless.
I know you looking for Qlik Sense and I gave you QVW but the Script will be same in both the tools.
Just copy paste the script you need into your QS app.
SA - Copy.qvw 162.8 K
I used my own data, your load was complicated but it's same load.
LOAD Distinct * INLINE [
Position, Name, Rotation, Incoming
Clerk, Maria, 1/5/2017, 8/7/2015
Clerk, John , 1/6/2020, 8/8/2017
Janitor, Chris, 1/7/2017, 8/9/2015
Janitor, Harry, 1/8/2017, 8/10/2017
Super, Nina, 1/9/2020, 8/11/2015
Super, Dave, 1/10/2019, 8/12/2017
Manager, Vern, 1/11/2017, 8/13/2015
if(Incoming>today(),Name) as V1,
if(Incoming<today(),Name) as V2
The resident load I did in script and in front end I use pivot table or you can use St table as well..
Current: only( aggr( V2, V1, V2))
Replacement: only( aggr( V1, V2, V1))
End result I got is below.
Edit: There can be a better way to do it I'm no expert in Qlik but someone else can help you to optimize it a better way?