5 Replies Latest reply: Sep 10, 2014 10:59 AM by anbu cheliyan RSS

    Join tables

    Juan Patricio Iorio

      Hi! I put the problem in the attached file, and in it its what I need:

       

      There is a table that have events, thats events has owner but only I can see it when the event is "TRANSFERIR"

       

      In the excel its clearly; thank you!!

          • Re: Join tables
            Juan Patricio Iorio

            Márcio, the problem is tha i dont have the first and last value for each ticket!

              • Re: Join tables
                anbu cheliyan

                Try this

                 

                Initial:

                Load ticket,actividad,fechainicio,fechafin,

                If( IsNull(grupodesde) Or grupodesde = '',If(Previous(grupohasta) = '',Peek(grupoactual),Previous(grupohasta)),

                grupodesde) As grupoactual, RowNo() As RowNo;

                Load * Inline [

                ticket,actividad,fechainicio,fechafin,grupodesde,grupohasta

                1,inicio,1/20/2010,1/20/2010,,

                1,adjunta,1/20/2010,1/20/2010,,

                1,transferir,1/21/2010,1/22/2010,g1,g2

                1,actualiza,1/22/2010,1/22/2010,,

                1,resuelto,1/22/2010,1/22/2010,,

                1,transferir,1/22/2010,1/22/2010,g2,g3

                1,reanudar,1/22/2010,1/22/2010,,

                1,resuelto,1/22/2010,1/22/2010,, ];

                NoConcatenate

                Final:

                Load ticket,actividad,fechainicio,fechafin,

                If( IsNull(grupoactual) Or grupoactual = '',Peek(grupoactual),grupoactual) As grupoactual Resident Initial Order by RowNo desc;

                 

                 

                Drop table Initial;

                  • Re: Join tables
                    Juan Patricio Iorio

                    Its nearest!

                    Please see the attached file (example2), the NSD = HD398723, the data correct is the column OK

                      • Re: Join tables
                        anbu cheliyan

                        Try this

                         

                        Initial:

                        Load ACTIVIDAD,

                             NSD,

                             TIEMPO_TOTAL,

                             FechaInicio,

                             FechaRes,

                             If( NSD <> Previous(NSD),GRUPODESDE,

                        If( IsNull(GRUPODESDE) Or GRUPODESDE = '',If( IsNull(Previous(GRUPOHASTA)) Or Previous(GRUPOHASTA) = '',Peek(grupoactual),Previous(GRUPOHASTA)),

                        GRUPODESDE)) As grupoactual, RowNo() As RowNo;

                        LOAD ACTIVIDAD,

                             NSD,

                             TIEMPO_TOTAL,

                             FechaInicio,

                             FechaRes,

                             GRUPODESDE,

                             GRUPOHASTA,

                             GRUPO_ACTUAL,

                             FLAGT,

                             OK

                        FROM

                        [C:\Documents and Settings\Administrator\Desktop\example2.xls]

                        (biff, embedded labels);

                         

                         

                         

                         

                        NoConcatenate

                        Final:

                        Load ACTIVIDAD,

                             NSD,

                             TIEMPO_TOTAL,

                             FechaInicio,

                             FechaRes,

                        If( IsNull(grupoactual) Or grupoactual = '',Peek(grupoactual),grupoactual) As grupoactual,

                        RowNo Resident Initial Order by RowNo desc;

                         

                         

                        Drop table Initial;