Discussion board where members can get started with Qlik Sense.
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
the max is seconday or it can go further than that?
you must use a where clause when you read it and create two different tables than you can make a join from one to another table.
from where (criteria 1)
Left Join (Table1)
name as secondary,
dateonboard as Secondarydateonboard
from where (criteria 2)
Try the below
RAW: LOAD * inline [PosID, Name, dateonboard123, John Alpha, 1/1/2016123, Jane Ball, 12/1/2016345, Chris Doe, 4/5/2018345, Rob Zombie, 6/1/2017678, Bon Jovi, 8/7/2015];
Temp:Load IF(Previous(PosID) = PosID, 1) AS Number, PosID, Name, dateonboardResident RAW;
DROP TABLE RAW;
DATA:Load PosID, Name as Primary, dateonboard AS Primarydateonboard Resident TempWHERE Number <> 1;
Load PosID, Name AS Secondary, dateonboard AS SecondarydateonboardResident TempWHERE Number = 1; DROP TABLE Temp;
yes, the min onboard date is the primary and max is the secondary.
what does Previous(PosID) do? if it's looking at the previous record, it doesn't necessarily load in order, unless I do an ORder by?? in the Raw data load?
Yes it looks at previous record.
RAW: LOAD * inline [PosID, Name, dateonboard123, John Alpha, 1/1/2016345, Chris Doe, 4/5/2018345, Rob Zombie, 6/1/2017678, Bon Jovi, 8/7/2015123, Jane Ball, 12/1/2016];
Temp:Load IF(PEEK((PosID),-1) = PosID, 1) AS Number, PosID, Name, dateonboardResident RAWOrder BY PosID, dateonboard;
PEEK is similar to previous, but works on the complete reordered table.
Maybe like this:
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
load min(dateonboard) as FirstDate, PosID Resident table group by PosID;
load PosID,dateonboard as FirstDate, Name as FirstName Resident table;
load max(dateonboard) as SecondDate, PosID Resident table group by PosID;
load PosID,dateonboard as SecondDate, Name as SecondName Resident table;
left Join (table1)
load * Resident table2;
drop Table table2;
Drop Table table;
load PosID,FirstName,date(FirstDate) as FirstDate,if(FirstName= SecondName, null(),SecondName) as SecondName ,if(FirstName= SecondName, null(), date(SecondDate)) as SecondDate Resident table1;
drop Table table1;
why are you all using the word "NoConcatenate"? is that a keyword that has to be used?
Qlik Sense perofmrs auto concatentation when 2 tables has the same columns name/number:
example : table 1 :
we want to create another table resident from table1 (our source is table1) but we want to perform some changes and then delete the first table:
load cola, colb, colc from..;
load cola,colb,colc where cola='aaa' resident table1;
dropt table table1;
but with this code, Qlik will see that table1 and table2 have the same colum number/names, so it will automtically concatentate the 2 tables into ONE TABLE!
if we drop table table1; we will see that table2 does not exist anymore because it has been attached the table1.
So, to make the 2 tables seperated and drop only the first one we add the noconcatenate:
Pleasemark correct the correct answer when your question has been asnwered and as helpful the helpful ones