Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
May be You need to set properly Your Date format, like
SET DateFormat='DD/MM/YYYY';
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
;
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;
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
;
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!
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
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 ! ! ! ! ! !
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:
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;
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;