Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

help joining data properly

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

34 Replies
OmarBenSalem

the max is seconday or it can go further than that?

Ralf_Heukäufer
Partner - Creator III
Partner - Creator III

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)

Mark_Little
Luminary
Luminary

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

Anonymous
Not applicable
Author

yes, the min onboard date is the primary and max is the secondary. 

Anonymous
Not applicable
Author

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?

Mark_Little
Luminary
Luminary

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

OmarBenSalem

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:

Capture.PNG

Anonymous
Not applicable
Author

why are you all using the word "NoConcatenate"?  is that a keyword that has to be used?

OmarBenSalem

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