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

[antoniotiman]

May be You need to set properly Your Date format, like

SET DateFormat='DD/MM/YYYY';

[Anonymous]

I've changed the structure of the data load.  I do multiple loads.  first I load the base data.  then I load the table again to create columns B and A grouped by PositionID.  but now I'm getting an invalid expression error at the below table.  it doesn't like the Concat.  I don't understand why because it worked when I had it further down in the load statement.  what is going on?

PrimarySecondaryTemp:
Load
*,
Concat("On Board Date",'|',"On Board Date") as B,
Concat("Emp Name",'|',"On Board Date") as A
Resident AsOfDateData
Group By PositionID
;

[Anonymous]

Please assist.  it is urgent that I complete this task.

I would think that the below should work, but I keep getting an "Invalid Expression" error.  obviously my understanding of the data model is incorrect.

My goal is to have all the data in one table called FinalData.

Groupby:
NoConcatenate
load
*,
Concat("On Board Date",'|',"On Board Date") as B,
    Concat("Emp Name",'|', "On Board Date") as A
Resident EncumberedFlags
group by PosID
;

Drop Table EncumberedFlags;

FinalData:
NoConcatenate
Load
*,
    SubField(A, '|', 1) as Primary,
    SubField(A, '|', 2) as Secondary,
    SubField(B, '|', 1) as OnboardDeparting,
    SubField(B, '|', 2) as OnboardIncoming
Resident Groupby
;

Drop Table Groupby;

[antoniotiman]

Groupby:

NoConcatenate

load

PosID,      // instead of *,

Concat("On Board Date",'|',"On Board Date") as B,

    Concat("Emp Name",'|', "On Board Date") as A

Resident EncumberedFlags

group by PosID

;

[Anonymous]

okay, so I messed up with my original question.  I was wrong about how I wanted the final table to look. 

here is the original data (very simplified):   

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                   !

Here is how I want the final data to look:

PosID            Name             dateonbrd      Primary             Primdate       Secondary             Secdate 

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

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

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

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

678               Jon Jovi            8/7/2015       Bon Jovi            8/7/2015         !                                     !

!                    Maria               10/1/2014     Maria                 10/1/2014      !                                      ! 

!                    Jeff                    2/1/2013       Jeff                     2/1/2013         !                                      !

!                    Becky             9/15/2010       Becky                 9/15/2010       !                                      ! 

012                  !                       !                     !                           !                        !                                      !

013                  !                       !                      !                           !                        !                                      !

I have a lot of data in my original table that needs to be preserved in my final table.  I'm doing a series of loads where I'm creating flags and such.  this is my final load.  I want to add primary, PrimDate, Secondary, SedDate to every single record. is this a left join on PosID?  I'd have to strip out the PosID='!' and then concatenate them back into the joined?  HELP!

[OmarBenSalem]

Maria, can you please try this and tell me if it answers your question?

//this is your original table:

table:

load * 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

];

table1:

NoConcatenate

load date(min(dateonboard)) as FirstDate,  PosID Resident table group by PosID;

left Join(table1)

load PosID,dateonboard as FirstDate, Name as FirstName Resident table;

table2:

NoConcatenate

load date(max(dateonboard)) as SecondDate,  PosID Resident table group by PosID;

left Join(table2)

load PosID,dateonboard as SecondDate, Name as SecondName Resident table;

left Join (table1)

load * Resident table2;

drop Table table2;

NoConcatenate

final:

load PosID,FirstName,date(FirstDate,'DD/MM/YYYY') as FirstDate,if(FirstName= SecondName, null(),SecondName) as SecondName ,if(FirstName= SecondName, null(), date(SecondDate,'DD/MM/YYYY')) as SecondDate Resident table1;

drop Table table1;

left join(table)

load * resident final;

drop table final;

Result

[Anonymous]

almost there...I need a fix for when the PosID='!'.  I don't want all of those to join on each other.  I should only have 187 thousand rows, but because I have the '!' in the list, I'm getting 12 million rows.  would I strip out BIN='!'? then concatenate them back into the finaldata table?

here is the original data (very simplified):   

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                   !

Here is how I want the final data to look:

PosID            Name             dateonbrd      Primary             Primdate       Secondary             Secdate 

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

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

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

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

678               Jon Jovi            8/7/2015       Bon Jovi            8/7/2015         !                                     !

!                    Maria               10/1/2014     Maria                 10/1/2014      !                                      ! 

!                    Jeff                    2/1/2013       Jeff                     2/1/2013         !                                      !

!                    Becky             9/15/2010       Becky                 9/15/2010       !                                      ! 

012                  !                       !                     !                           !                        !                                      !

013                  !                       !                      !                           !                        !                                      !

[OmarBenSalem]

I see:

then add this after the other script above:

noconcatenate

tableFinale:

load PosID,Name,dateonboard, Name as FirstName, dateonboard as FirstDate, null() as SecondDate, null() as SecondName;

load * Resident table Where PosID= '!';

Concatenate

Load * Resident table where PosID<>'!';

drop Table table;

result:

[Anonymous]

this is my script and the qlik engine does not like it.  where did I go wrong?  it's loading everything until the very last couple of loads.  it's loading 12million + records and it never reconciles.  I should only have 187k records.

table1:
NoConcatenate
load
date(min("On Board Date")) as FirstDate, 
PosID
Resident OriginalData
where PosID <> '!'
group by PosID
;

left Join(table1)
load
PosID,
"On Board Date" as FirstDate,
"Emp Name" as FirstName
Resident OriginalData
;

table2:
NoConcatenate
load
date(max("On Board Date")) as SecondDate, 
PosID
Resident OriginalData
where PosID <> '!'
group by PosID
;

left Join(table2)
load
PosID,
"On Board Date" as SecondDate,
"Emp Name" as SecondName
Resident OriginalData;

left Join (table1)
load * Resident table2;
drop Table table2;

NoConcatenate
NonNullPosID:
load PosID,
FirstName,
date(FirstDate,'DD/MM/YYYY') as FirstDate,
if(FirstName= SecondName, null(),SecondName) as SecondName ,
if(FirstName= SecondName, null(),
date(SecondDate,'DD/MM/YYYY')) as SecondDate
Resident table1
;

drop Table table1;

noconcatenate
NullPosID:
load
*,
"On Board Date" as FirstDate,
"Emp Name" as FirstName,
null() as SecondDate,
null() as SecondName
Resident OriginalData
Where PosID = '!'
;

NoConcatenate
FinalData:
Load
*
Resident OriginalData
Where PosID <> '!'
;

drop Table OriginalData;

left join (FinalData)
load * resident NonNullPosID;

drop table NonNullPosID;

Concatenate
Load
*
Resident NullPosID;

drop Table NullBIN;

[Anonymous]

hi, Omar,

below is my script.  it is working....ALMOST.  the problem is that I have multiple people with the same onboard date.  so, for every two people that have the same position id and same onboard date, I'm getting 8 records.  I need another step to use alphabetical order in that instance.  do I have to do another series of loads after the PrimarySecondary table?  or can I somehow do that within the PrimaryTemp and SecondaryTemp tables?

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

left Join(PrimaryTemp)
load
PosID,
"On Board Date" as FirstDate,
"Emp Name" as FirstName
Resident FinalData
;

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

left Join(SecondaryTemp)
load
PosID,
"On Board Date" as SecondDate,
"Emp Name" as SecondName
Resident FinalData;

left Join (PrimaryTemp)
load * Resident SecondaryTemp;
drop Table SecondaryTemp;

NoConcatenate
PrimarySecondary:
load PosID,
FirstName,
date(FirstDate,'DD/MM/YYYY') as FirstDate,
if(FirstName= SecondName, null(),SecondName) as SecondName ,
if(FirstName= SecondName, null(),
date(SecondDate,'DD/MM/YYYY')) as SecondDate
Resident PrimaryTemp
;

drop Table PrimaryTemp;

left join (FinalData)
load * resident PrimarySecondary;

drop table PrimarySecondary;