15 Replies Latest reply: Dec 8, 2017 9:42 AM by omar bensalem RSS

    Join a table where the max value is less than or equal to joined table

    David Le

      Hi,  I have spent most of the day searching for a solution and cant seem to find anything.

      I am trying to determine an employees position code based on a point in time

       

      My tables are

       

      Table1

      Month DateEmployeeID
      12/20161
      1/20171
      2/20171
      3/20171

       

       

      Table2

       

      Table 1

      Month DateEmployeeIDJobCode
      12/20161A
      2/20171B
      3/20171

      C

       

      I want to display for 1/2017 that the employee jobcode is still "A" and since 1/2017 is not in table2 I cant get it to work

       

      Thanks in advance

        • Re: Join a table where the max value is less than or equal to joined table
          omar bensalem

          Table1:

          load Capitalize(date(Date#(MonthDate,'MM/YYYY'),'MMMM YYYY')) as MonthDate, EmployeeID Inline [

          MonthDate, EmployeeID

          12/2016, 1

          1/2017, 1

          2/2017, 1

          3/2017, 1 ];

           

          Left Join(Table1)

          load Capitalize(date(Date#(MonthDate,'MM/YYYY'),'MMMM YYYY')) as MonthDate, EmployeeID, JobCode Inline [

          MonthDate, EmployeeID, JobCode

          12/2016, 1, A

          2/2017, 1, B

          3/2017, 1,   C

          ];

           

          NoConcatenate

          FinalTable:

          load

          MonthDate, EmployeeID,if (len(Trim(JobCode))=0,Peek(JobCode),JobCode) as JobCode  Resident

          Table1 Order By MonthDate ;

          drop table Table1;

          Capture.PNG

            • Re: Join a table where the max value is less than or equal to joined table
              David Le

              Thanks but that is not what I am looking for.

               

              I want January to be JobCode "A" since he didn't get to B until February.

                • Re: Join a table where the max value is less than or equal to joined table
                  omar bensalem

                  My bad !

                   

                  Table1:

                  load Capitalize(date(Date#(MonthDate,'MM/YYYY'),'MMMM YYYY')) as MonthDate, EmployeeID Inline [

                  MonthDate, EmployeeID

                  12/2016, 1

                  1/2017, 1

                  2/2017, 1

                  3/2017, 1 ];

                   

                  Left Join(Table1)

                  load Capitalize(date(Date#(MonthDate,'MM/YYYY'),'MMMM YYYY')) as MonthDate, EmployeeID, JobCode Inline [

                  MonthDate, EmployeeID, JobCode

                  12/2016, 1, A

                  2/2017, 1, B

                  3/2017, 1,   C

                  ];

                   

                  NoConcatenate

                  FinalTable:

                  load *, num(MonthDate) as numMonth  ;

                  load

                  MonthDate, EmployeeID, JobCode  Resident Table1 ;

                   

                  drop table Table1;

                   

                  NoConcatenate

                  final:

                  load MonthDate, EmployeeID,numMonth, if (len(Trim(JobCode))=0,Peek(JobCode),JobCode) as JobCode Resident FinalTable Order by numMonth;

                  drop field numMonth;

                  Drop Table FinalTable;

                   

                   

                  result:

                  Capture.PNG

                    • Re: Join a table where the max value is less than or equal to joined table
                      David Le

                      Worked Perfectly.  Thanks you so much for the help and your time.  Appreciated

                      • Re: Join a table where the max value is less than or equal to joined table
                        David Le

                        Omar,

                         

                        Perhaps you can help me here.

                         

                        I tried with more data and it doesn't result in the same.  I tested to see with a record with the employee having a history record with a date less than the main table.  What happens is that the left join excludes the prior records so I don't know what the job code is.

                         

                        Table1:

                        load Capitalize(date(Date#(MonthDate,'MM/YYYY'),'MMMM YYYY')) as MonthDate, EmployeeID Inline [

                        MonthDate, EmployeeID

                        12/2016, 1

                        1/2017, 1

                        2/2017, 1

                        3/2017, 1
                        12/2016, 2

                        1/2017, 2

                        2/2017, 2

                        3/2017, 2 ];

                         

                        Left Join(Table1)

                        load Capitalize(date(Date#(MonthDate,'MM/YYYY'),'MMMM YYYY')) as MonthDate, EmployeeID, JobCode Inline [

                        MonthDate, EmployeeID, JobCode

                        12/2012, 1, A

                        2/2015, 1, A

                        3/2017, 1,  C

                        12/2016, 2, A

                         

                        3/2017, 2,   C

                        ];

                         

                        NoConcatenate

                        FinalTable:

                        load *, num(MonthDate) as numMonth  ;

                        load

                        MonthDate, EmployeeID, JobCode  Resident Table1 ;

                         

                        drop table Table1;

                         

                        NoConcatenate

                        final:

                        load MonthDate, EmployeeID,numMonth, if (len(Trim(JobCode))=0,Peek(JobCode),JobCode) as JobCode Resident FinalTable Order by EmployeeID, numMonth;

                        drop field numMonth;

                        Drop Table FinalTable;

                         

                        Please see script

                         

                          • Re: Join a table where the max value is less than or equal to joined table
                            omar bensalem

                            then in ur table:

                            Table1:

                            load Capitalize(date(Date#(MonthDate,'MM/YYYY'),'MMMM YYYY')) as MonthDate, EmployeeID Inline [

                            MonthDate, EmployeeID

                            12/2016, 1

                            1/2017, 1

                            2/2017, 1

                            3/2017, 1

                            12/2016, 2

                            1/2017, 2

                            2/2017, 2

                            3/2017, 2 ];

                             

                            Left Join(Table1)

                            load Capitalize(date(Date#(MonthDate,'MM/YYYY'),'MMMM YYYY')) as MonthDate, EmployeeID, JobCode Inline [

                            MonthDate, EmployeeID, JobCode

                            12/2012, 1, A

                            2/2015, 1, A

                            3/2017, 1,  C

                            12/2016, 2, A

                             

                            3/2017, 2,   C

                            ];

                             

                            NoConcatenate

                            FinalTable:

                            load *, num(MonthDate) as numMonth  ;

                            load

                            MonthDate, EmployeeID, JobCode  Resident Table1 ;

                             

                            drop table Table1;

                             

                            NoConcatenate

                            final:

                            load MonthDate, EmployeeID,numMonth, if (len(Trim(JobCode))=0,Peek(JobCode),JobCode) as JobCode Resident FinalTable Order by numMonth;

                            drop field numMonth;

                            Drop Table FinalTable;

                             

                             

                            then in ur table:

                            as dimension: EmployeeID

                            MonthDate

                            as measure:

                            =if(IsNull( JobCode),below(only((JobCode))),JobCode)


                            and sort as follow:

                            Capture.PNG

                             

                            result:

                            Capture.PNG

                            Capture.PNG

                            Capture.PNG