34 Replies Latest reply: Oct 31, 2017 2:17 PM by Maria Harmening RSS

    help joining data properly

    Maria Harmening

      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

        • Re: help joining data properly
          omar bensalem

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

          • Re: help joining data properly
            Ralf Heukäufer

            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)

            • Re: help joining data properly
              Mark Little

              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

                • Re: help joining data properly
                  Maria Harmening

                  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?

                    • Re: help joining data properly
                      Mark Little

                      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

                  • Re: help joining data properly
                    omar bensalem

                    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

                      • Re: help joining data properly
                        Maria Harmening

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

                          • Re: help joining data properly
                            omar bensalem

                            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

                              • Re: help joining data properly
                                Maria Harmening

                                Below are my scripts.  I'm getting an error at "left Join (RawLeft)"  what am I doing wrong?

                                 

                                RawLeft:
                                NoConcatenate
                                load
                                PosID,
                                min("On Board Date") as OnboardDeparting
                                Resident OriginalData
                                Where PosID<> '!'
                                group by PosID
                                ;

                                 

                                left Join (RawLeft)
                                load
                                PosID,
                                min("On Board Date") as OnboardDeparting,
                                "Emp Name" as Primary


                                Resident OriginalData
                                Where PosID <> '!'
                                ;

                                 

                                 

                                RawRight:
                                NoConcatenate
                                load
                                PosID,
                                max("On Board Date") as OnboardIncoming
                                Resident OriginalData
                                Where PosID<> '!'
                                group by PosID
                                ;

                                 

                                left Join (RawRight)
                                load
                                PosID,
                                max("On Board Date") as OnboardIncoming,
                                "Emp Name" as Secondary,
                                    Resident OriginalData
                                    Where PosID<> '!'
                                    ;

                                left Join (RawLeft)

                                load * Resident RawRight;

                                drop Table RawRight;

                                IncomingDeparting:
                                NoConcatenate
                                load
                                PosID,
                                Primary as NameDeparting,
                                OnboardDeparting,
                                if(Primary = Secondary, null(),Secondary) as NameIncoming,
                                if(Primary = Secondary, null(), OnboardIncoming) as OnboardIncoming
                                Resident RawLeft;

                                drop Table RawLeft;

                          • Re: help joining data properly
                            Antonio Mancini

                            Hi Maria,

                            may be this

                            Temp:
                            LOAD PosID,Concat(dateonboard,'|',dateonboard) as B,Concat(Name,'|',dateonboard) as A
                            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
                            ]
                            Group By PosID;

                            LOAD PosID,SubField(A,'|',1) as Primary,SubField(A,'|',2) as Secondary,
                            SubField(B,'|',1) as Primarydateonboard,SubField(B,'|',2) as Secondarydateonboard
                            Resident Temp;
                            Drop Table
                            Temp;

                            Regards,

                            Antonio

                            • Re: help joining data properly
                              Maria Harmening

                              Concat(dateonboard,'|',dateonboard) as B..what is that doing?

                              And how about the subfield function?  I'm struggling to follow because my table are excel, and the examples being provided are being loaded inline. My real life file is not as simple, but I think it's sufficient.

                              • Re: help joining data properly
                                Massimo Grossi

                                1.png

                                 

                                tmp:

                                LOAD

                                     PosID,

                                     Name,

                                     dateonboard

                                FROM excel.xlsx (ooxml, embedded labels, table is Foglio1);

                                 

                                tmp2:

                                NoConcatenate LOAD

                                     AutoNumber(PosID & dateonboard, PosID) as ID,

                                     PosID,

                                     Name,

                                     dateonboard

                                Resident tmp

                                order by PosID, dateonboard;

                                DROP Table tmp;

                                 

                                Result:

                                LOAD

                                     PosID,

                                     Name as Primary,

                                     dateonboard as Primarydateonboard

                                Resident tmp2

                                Where ID = 1;

                                 

                                left join (Result)

                                LOAD

                                     PosID,

                                     Name as Secondary,

                                     dateonboard as Secondarydateonboard

                                Resident tmp2

                                Where ID = 2;

                                DROP Table tmp2;

                                • Re: help joining data properly
                                  Maria Harmening

                                  I've tried so many different things, and I just can't get it.  here is the data file.  Help!  I'm at a loss

                                    • Re: help joining data properly
                                      Antonio Mancini

                                      Temp:
                                      LOAD PosID,Concat([On Board Date],'|',[On Board Date]) as B,Concat(EmpName,'|',[On Board Date]) as A
                                      FROM
                                      [LossesGainsStrippedRQlik.xlsx]
                                      (ooxml, embedded labels, table is Data)
                                      Group By PosID;

                                      LOAD PosID,SubField(A,'|',1) as Primary,SubField(A,'|',2) as Secondary,
                                      SubField(B,'|',1) as Primarydateonboard,SubField(B,'|',2) as Secondarydateonboard
                                      Resident Temp;
                                      Drop Table
                                      Temp; 

                                    • Re: help joining data properly
                                      Maria Harmening

                                      I used the below code.  i did not get the successful result from above.  I'm getting the same person in the primary and secondary.  basically the primary = secondary and primaryonboard=secondaryonboard.  could it be because I'm loading the Temp table from a Resident table?

                                       

                                      Temp:
                                      NoConcatenate
                                      load
                                      PosID,
                                      Concat("On Board Date",'|',"On Board Date") as B,
                                          Concat("Emp Name",'|',"On Board Date") as A
                                      Resident MyResidentTable
                                      Where PosID <> '!'
                                      group by PosID
                                      ;

                                      IncomingDeparting:
                                      Load
                                      PosID,
                                          SubField(A, '|', 1) as Primary,
                                          SubField(A, '|', 2) as Secondary,
                                          SubField(B, '|', 1) as OnboardPrimary,
                                          SubField(B, '|', 2) as OnboardSecondary
                                      Resident Temp
                                      ;

                                      drop table Temp;

                                      • Re: help joining data properly
                                        Maria Harmening

                                        i believe that i need to join the IncomingDeparting table with the MyResidentTable.  the MyResidentTable is my "Main" table that contains all of the fields that i need for my entire application..  so if for every BIN record, i could have a Primary and Secondary, that would be the solution.  how could i achieve that final step? 

                                        • Re: help joining data properly
                                          Maria Harmening

                                          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
                                          ;

                                          • Re: help joining data properly
                                            Maria Harmening

                                            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;

                                            • Re: help joining data properly
                                              Maria Harmening

                                               

                                              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!

                                               

                                               

                                               

                                               

                                               

                                               

                                               

                                                • Re: help joining data properly
                                                  omar bensalem

                                                  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

                                                  Capture.PNG

                                                    • Re: help joining data properly
                                                      Maria Harmening

                                                      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                  !                       !                      !                           !                        !                                      !

                                                        • Re: help joining data properly
                                                          omar bensalem

                                                          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:

                                                          Capture.PNG

                                                          Capture.PNG

                                                            • Re: help joining data properly
                                                              Maria Harmening

                                                              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;

                                                      • Re: help joining data properly
                                                        Maria Harmening

                                                        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;

                                                          • Re: help joining data properly
                                                            omar bensalem

                                                            Please change 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

                                                            !                        Maria                 10/1/2014

                                                            !                        Jeff                     2/1/2013

                                                            !                        Becky                9/15/2010

                                                            012                   !

                                                            013                   !


                                                            into sthing causing a problem to directly work on it; and show me what you want to have as a result.


                                                            ps: many people have been investing time to help you, it's time to mark some responses as helpful; don't you think?

                                                              • Re: help joining data properly
                                                                Maria Harmening

                                                                yes, you have all been extremely helpful.  I didn't realize that marking as correct or as helpful affects compensation or metrics or even just the desire to help someone?  sorry about that.  but yes, I will mark as helpful. I thank you all very much for taking the time.  unfortunately, there are no qlik experts that work with me.  Everyone here is learning as we go, and I'm the only one that's really manipulating load scripts.  it has taken me some time to wrap my mind around it.

                                                                 

                                                                I added chuck and Omar and dennis and pete

                                                                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                   !

                                                                014                   Omar                8/1/2016

                                                                014                    Chuck               8/1/2016

                                                                015                   Dennis              4/6/2015

                                                                015                   pete                   4/6/2015

                                                                  • Re: help joining data properly
                                                                    omar bensalem

                                                                    Everyone kept on anwering while you havn't marked any answer as helpful; which means that my remark is not meant to gain some compensation and desire to help; but it will help some users in the future who will be looking for this thread at google and search for right/helpful answers..

                                                                    so this part (the 8 times thing) :

                                                                    Capture.PNG

                                                                     

                                                                    how should it become?

                                                                      • Re: help joining data properly
                                                                        Maria Harmening

                                                                        There have been quite a few responses.  I could not complete this task without this forum.    you should get compensated.

                                                                         

                                                                        PosID      Name              dateonboard            FirstDate            FirstName         SecondDate           SecondName

                                                                        014          Omar               8/1/2016                  8/1/2016             Chuck                8/1/2016                Omar

                                                                        014          Chuck              8/1/2016                  8/1/2016             Chuck                8/1/2016                Omar

                                                                         

                                                                        So, if two people in the same position have the same onboard date, make the primary the earliest in the alphabet and the secondary the next in the alphabet

                                                                • Re: help joining data properly
                                                                  Maria Harmening

                                                                  After a lot of tries, I believe that this script works.  it puts the earliest person in the position as the primary and the next person in the position as the secondary.  if the two people have the same on board date, it does it by alphabetical order.

                                                                   

                                                                  PrimaryTemp:
                                                                  NoConcatenate
                                                                  load
                                                                  MinString("Emp Name") as PrimaryTemp_MinName,
                                                                  date(min("On Board Date")) as PrimaryTemp_MinDate,
                                                                  PosID
                                                                  Resident FinalData
                                                                  where PosID <> '!'
                                                                  group by PosID
                                                                  ;

                                                                  left Join(PrimaryTemp)
                                                                  load
                                                                  PosID,
                                                                  "On Board Date" as PrimaryTemp_MinDate,
                                                                  "Emp Name" as FirstName,
                                                                      "Rotation Date (PRD)" as FirstPRD
                                                                  Resident FinalData
                                                                  Where PosID <> '!'
                                                                  ;

                                                                  SecondaryTemp:
                                                                  NoConcatenate
                                                                  load
                                                                  MaxString("Emp Name") as SecondaryTemp_MaxName,
                                                                  date(max("On Board Date")) as SecondaryTemp_MaxDate, 
                                                                  PosID
                                                                  Resident FinalData
                                                                  where PosID <> '!'
                                                                  group by PosID
                                                                  ;

                                                                  left Join(SecondaryTemp)
                                                                  load
                                                                  PosID,
                                                                  "On Board Date" as SecondaryTemp_MaxDate,
                                                                  "Emp Name" as SecondName
                                                                  Resident FinalData
                                                                  where PosID <> '!';

                                                                  left Join (PrimaryTemp)
                                                                  load * Resident SecondaryTemp
                                                                  ;

                                                                  drop Table SecondaryTemp;

                                                                  NoConcatenate
                                                                  PrimarySecondary:
                                                                  load
                                                                  PosID,
                                                                  if(PrimaryTemp_MinName<>SecondaryTemp_MaxName, if(SecondaryTemp_MaxDate=PrimaryTemp_MinDate, SecondaryTemp_MaxName, SecondName),
                                                                    '!') as Secondary,

                                                                  if(PrimaryTemp_MinName=SecondaryTemp_MaxName, '!', Date(SecondaryTemp_MaxDate, 'MM/DD/YYYY')) as SecondaryOnboard,

                                                                  if(PrimaryTemp_MinName<>SecondaryTemp_MaxName, if(SecondaryTemp_MaxDate=PrimaryTemp_MinDate, PrimaryTemp_MinName, FirstName),
                                                                    FirstName) as Primary,
                                                                  Date(PrimaryTemp_MinDate, 'MM/DD/YYYY') as PrimaryOnboard,
                                                                  Date(FirstPRD, 'MM/DD/YYYY') as PrimaryPRD
                                                                  Resident PrimaryTemp
                                                                  ;

                                                                  drop Table PrimaryTemp;


                                                                  left join (FinalData)
                                                                  load
                                                                  Distinct * resident PrimarySecondary
                                                                  ;