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

[Anonymous]

Below are my scripts.  I'm getting an error at "left Join (RawLeft)"  what am I doing wrong?

RawLeft:
NoConcatenate
load
PosID,
min("On Board Date") as OnboardDeparting
Resident OriginalData
Where PosID<> '!'
group by PosID
;

left Join (RawLeft)
load
PosID,
min("On Board Date") as OnboardDeparting,
"Emp Name" as Primary

Resident OriginalData
Where PosID <> '!'
;

RawRight:
NoConcatenate
load
PosID,
max("On Board Date") as OnboardIncoming
Resident OriginalData
Where PosID<> '!'
group by PosID
;

left Join (RawRight)
load
PosID,
max("On Board Date") as OnboardIncoming,
"Emp Name" as Secondary,
    Resident OriginalData
    Where PosID<> '!'
    ;

left Join (RawLeft)

load * Resident RawRight;

drop Table RawRight;

IncomingDeparting:
NoConcatenate
load
PosID,
Primary as NameDeparting,
OnboardDeparting,
if(Primary = Secondary, null(),Secondary) as NameIncoming,
if(Primary = Secondary, null(), OnboardIncoming) as OnboardIncoming
Resident RawLeft;

drop Table RawLeft;

[antoniotiman]

Hi Maria,

may be this

Temp:
LOAD PosID,Concat(dateonboard,'|',dateonboard) as B,Concat(Name,'|',dateonboard) as A
Inline [
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
]Group By PosID;

LOAD PosID,SubField(A,'|',1) as Primary,SubField(A,'|',2) as Secondary,
SubField(B,'|',1) as Primarydateonboard,SubField(B,'|',2) as Secondarydateonboard
Resident Temp;
Drop Table Temp;

Regards,

Antonio

[Anonymous]

Concat(dateonboard,'|',dateonboard) as B..what is that doing?

And how about the subfield function?  I'm struggling to follow because my table are excel, and the examples being provided are being loaded inline. My real life file is not as simple, but I think it's sufficient.

[Anonymous]

Concat(dateonboard,'|',dateonboard) as B..what is that doing?

And how about the subfield function?  I'm struggling to follow because my table are excel, and the examples being provided are being loaded inline. My real life file is not as simple, but I think it's sufficient.

[antoniotiman]

Concat groups values, 3rd parameter sorts values by date.

Subfield separates fields.

You replace inline with Your Excel file or can You provide File ?

[maxgro]

tmp:

LOAD

     PosID,

     Name,

     dateonboard

FROM excel.xlsx (ooxml, embedded labels, table is Foglio1);

tmp2:

NoConcatenate LOAD

     AutoNumber(PosID & dateonboard, PosID) as ID,

     PosID,

     Name,

     dateonboard

Resident tmp

order by PosID, dateonboard;

DROP Table tmp;

Result:

LOAD

     PosID,

     Name as Primary,

     dateonboard as Primarydateonboard

Resident tmp2

Where ID = 1;

left join (Result)

LOAD

     PosID,

     Name as Secondary,

     dateonboard as Secondarydateonboard

Resident tmp2

Where ID = 2;

DROP Table tmp2;

[Anonymous]

I've tried so many different things, and I just can't get it.  here is the data file.  Help!  I'm at a loss

[antoniotiman]

Temp:
LOAD PosID,Concat([On Board Date],'|',[On Board Date]) as B,Concat(EmpName,'|',[On Board Date]) as A
FROM
[LossesGainsStrippedRQlik.xlsx]
(ooxml, embedded labels, table is Data)
Group By PosID;

LOAD PosID,SubField(A,'|',1) as Primary,SubField(A,'|',2) as Secondary,
SubField(B,'|',1) as Primarydateonboard,SubField(B,'|',2) as Secondarydateonboard
Resident Temp;
Drop Table Temp; 

[Anonymous]

I used the below code.  i did not get the successful result from above.  I'm getting the same person in the primary and secondary.  basically the primary = secondary and primaryonboard=secondaryonboard.  could it be because I'm loading the Temp table from a Resident table?

Temp:
NoConcatenate
load
PosID,
Concat("On Board Date",'|',"On Board Date") as B,
    Concat("Emp Name",'|',"On Board Date") as A
Resident MyResidentTable
Where PosID <> '!'
group by PosID
;

IncomingDeparting:
Load
PosID,
    SubField(A, '|', 1) as Primary,
    SubField(A, '|', 2) as Secondary,
    SubField(B, '|', 1) as OnboardPrimary,
    SubField(B, '|', 2) as OnboardSecondary
Resident Temp
;

drop table Temp;

[Anonymous]

i believe that i need to join the IncomingDeparting table with the MyResidentTable.  the MyResidentTable is my "Main" table that contains all of the fields that i need for my entire application..  so if for every BIN record, i could have a Primary and Secondary, that would be the solution.  how could i achieve that final step?