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
the max is seconday or it can go further than that?
Hello,
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.
Table1:
Load
*
from where (criteria 1)
Left Join (Table1)
Table2:
Load
name as secondary,
dateonboard as Secondarydateonboard
from where (criteria 2)
HI,
Try the below
RAW:
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
];
NoConcatenate
Temp:
Load
IF(Previous(PosID) = PosID,
1) AS Number,
PosID,
Name,
dateonboard
Resident RAW;
DROP TABLE RAW;
NoConcatenate
DATA:
Load
PosID,
Name as Primary,
dateonboard AS Primarydateonboard
Resident Temp
WHERE Number <> 1;
LEFT JOIN(DATA)
Load
PosID,
Name AS Secondary,
dateonboard AS Secondarydateonboard
Resident Temp
WHERE Number = 1;
DROP TABLE Temp;
Mark
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?
Hi
Yes it looks at previous record.
Try this
RAW:
LOAD * inline [
PosID, Name, dateonboard
123, John Alpha, 1/1/2016
345, Chris Doe, 4/5/2018
345, Rob Zombie, 6/1/2017
678, Bon Jovi, 8/7/2015
123, Jane Ball, 12/1/2016
];
NoConcatenate
Temp:
Load
IF(PEEK((PosID),-1) = PosID,
1) AS Number,
PosID,
Name,
dateonboard
Resident RAW
Order BY PosID, dateonboard;
DROP TABLE RAW;
NoConcatenate
DATA:
Load
PosID,
Name as Primary,
dateonboard AS Primarydateonboard
Resident Temp
WHERE Number <> 1;
LEFT JOIN(DATA)
Load
PosID,
Name AS Secondary,
dateonboard AS Secondarydateonboard
Resident Temp
WHERE Number = 1;
DROP TABLE Temp;
PEEK is similar to previous, but works on the complete reordered table.
Mark
Maybe like this:
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 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 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;
Drop Table table;
NoConcatenate
final:
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;
result:
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 :
colA,ColB,ColC
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:
so:
table 1
load cola, colb, colc from..;
table2:
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:
table 1
load cola, colb, colc from..;
NOCONCATENATE
table2:
load cola,colb,colc where cola='aaa' resident table1;
dropt table table1;
Pleasemark correct the correct answer when your question has been asnwered and as helpful the helpful ones