help joining data properly

[Anonymous]

i have an excel file that looks like this:

PosID             Name              dateonboard

123                 John Alpha      1/1/2016

123                 Jane Ball          12/1/2016

345                 Chris Doe         4/5/2018

345                 Rob Zombie      6/1/2017

678                 Bon Jovi            8/7/2015

I want the data to look like this:

PosID             Primary             Primarydateonboard       Secondary                   Secondarydateonboard

123                 John Alpha      1/1/2016                           Jane Ball                      12/1/2016

345                 Rob Zombie      6/1/2017                         Chris Doe                      4/5/2018

678                 Jon Jovi            8/7/2015

[OmarBenSalem]

Please change this :

PosID                Name              dateonboard

123                   John Alpha      1/1/2016

123                   Jane Ball          12/1/2016

345                   Chris Doe         4/5/2018

345                   Rob Zombie      6/1/2017

678                   Bon Jovi            8/7/2015

!                        Maria                 10/1/2014

!                        Jeff                     2/1/2013

!                        Becky                9/15/2010

012                   !

013                   !

into sthing causing a problem to directly work on it; and show me what you want to have as a result.

ps: many people have been investing time to help you, it's time to mark some responses as helpful; don't you think?

[Anonymous]

yes, you have all been extremely helpful.  I didn't realize that marking as correct or as helpful affects compensation or metrics or even just the desire to help someone?  sorry about that.  but yes, I will mark as helpful. I thank you all very much for taking the time.  unfortunately, there are no qlik experts that work with me.  Everyone here is learning as we go, and I'm the only one that's really manipulating load scripts.  it has taken me some time to wrap my mind around it.

I added chuck and Omar and dennis and pete

PosID                Name              dateonboard

123                   John Alpha      1/1/2016

123                   Jane Ball          12/1/2016

345                   Chris Doe         4/5/2018

345                   Rob Zombie      6/1/2017

678                   Bon Jovi            8/7/2015

!                        Maria                 10/1/2014

!                        Jeff                     2/1/2013

!                        Becky                9/15/2010

012                   !

013                   !

014                   Omar                8/1/2016

014                    Chuck               8/1/2016

015                   Dennis              4/6/2015

015                   pete                   4/6/2015

[OmarBenSalem]

Everyone kept on anwering while you havn't marked any answer as helpful; which means that my remark is not meant to gain some compensation and desire to help; but it will help some users in the future who will be looking for this thread at google and search for right/helpful answers..

so this part (the 8 times thing) :

how should it become?

[Anonymous]

There have been quite a few responses.  I could not complete this task without this forum.    you should get compensated.

PosID      Name              dateonboard            FirstDate            FirstName         SecondDate           SecondName

014          Omar               8/1/2016                  8/1/2016             Chuck                8/1/2016                Omar

014          Chuck              8/1/2016                  8/1/2016             Chuck                8/1/2016                Omar

So, if two people in the same position have the same onboard date, make the primary the earliest in the alphabet and the secondary the next in the alphabet

[Anonymous]

After a lot of tries, I believe that this script works.  it puts the earliest person in the position as the primary and the next person in the position as the secondary.  if the two people have the same on board date, it does it by alphabetical order.

PrimaryTemp:
NoConcatenate
load
MinString("Emp Name") as PrimaryTemp_MinName,
date(min("On Board Date")) as PrimaryTemp_MinDate,
PosID
Resident FinalData
where PosID <> '!'
group by PosID
;

left Join(PrimaryTemp)
load
PosID,
"On Board Date" as PrimaryTemp_MinDate,
"Emp Name" as FirstName,
    "Rotation Date (PRD)" as FirstPRD
Resident FinalData
Where PosID <> '!'
;

SecondaryTemp:
NoConcatenate
load
MaxString("Emp Name") as SecondaryTemp_MaxName,
date(max("On Board Date")) as SecondaryTemp_MaxDate, 
PosID
Resident FinalData
where PosID <> '!'
group by PosID
;

left Join(SecondaryTemp)
load
PosID,
"On Board Date" as SecondaryTemp_MaxDate,
"Emp Name" as SecondName
Resident FinalData
where PosID <> '!';

left Join (PrimaryTemp)
load * Resident SecondaryTemp
;

drop Table SecondaryTemp;

NoConcatenate
PrimarySecondary:
load
PosID,
if(PrimaryTemp_MinName<>SecondaryTemp_MaxName, if(SecondaryTemp_MaxDate=PrimaryTemp_MinDate, SecondaryTemp_MaxName, SecondName),
  '!') as Secondary,

if(PrimaryTemp_MinName=SecondaryTemp_MaxName, '!', Date(SecondaryTemp_MaxDate, 'MM/DD/YYYY')) as SecondaryOnboard,

if(PrimaryTemp_MinName<>SecondaryTemp_MaxName, if(SecondaryTemp_MaxDate=PrimaryTemp_MinDate, PrimaryTemp_MinName, FirstName),
  FirstName) as Primary,
Date(PrimaryTemp_MinDate, 'MM/DD/YYYY') as PrimaryOnboard,
Date(FirstPRD, 'MM/DD/YYYY') as PrimaryPRD
Resident PrimaryTemp
;

drop Table PrimaryTemp;

left join (FinalData)
load
Distinct * resident PrimarySecondary
;