Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

mharm
Contributor

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
Esteemed Contributor

Re: help joining data properly

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

Ralf_Heukäufer
Contributor III

Re: help joining data properly

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)

mark6505
Valued Contributor III

Re: help joining data properly

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

mharm
Contributor

Re: help joining data properly

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

mharm
Contributor

Re: help joining data properly

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?

mark6505
Valued Contributor III

Re: help joining data properly

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
Esteemed Contributor

Re: help joining data properly

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

mharm
Contributor

Re: help joining data properly

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

OmarBenSalem
Esteemed Contributor

Re: help joining data properly

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

Community Browser