Re: help joining data properly - Page 3 - Qlik Community

Anonymous · ‎2017-10-20

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 · ‎2017-10-23

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

SET DateFormat='DD/MM/YYYY';

Anonymous · ‎2017-10-23

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 · ‎2017-10-23

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 · ‎2017-10-23

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 · ‎2017-10-25

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 · ‎2017-10-25

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 · ‎2017-10-25

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 · ‎2017-10-25

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 · ‎2017-10-25

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 · ‎2017-10-27

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;