5 Replies Latest reply: Oct 22, 2015 1:14 PM by john maldonado RSS

    fill the field in a table based on the contents of a field in another table

      Please your help. I need to fill the field in a table, depending on the contents of the field from another table, previously loaded. Please could you tell me what the function or procedure appropriate for this purpose?

        • Re: fill the field in a table based on the contents of a field in another table
          Peter Cammaert

          Create a Mapping Table with data from the table that is loaded first.

          Then use applymap() to search for the appropriate field content to be used in the field of the second table.

           

          Check QV Desktop help for more information.

          • Re: fill the field in a table based on the contents of a field in another table

            Estimado Peter:

            Previamente intenté solucionar el problema utilizando LOOKUP, sin embargo tengo un problema, el campo con el que se concatenan las dos tablas con [Nº pers.] Y [Fecha Rol].

             

            Estos campos a su vez se llenan en cada tabla mediante la utilización de la función INTERVALMATCH, misma que se ejecuta luego de haber cargado la tabla.

             

            Existe alguna forma de ejecutar APPLYMAP luego de haber ejecutado la sentencia INTERVALMATCH y de ser así, el índice de busqueda puede ser el resultado de la concatenación de [Nº pers.]&[Fecha Rol]

             

             

            "PA CAMPAÑAS": 

            LOAD  

                 [Nº pers.] AS [Nº pers.] , 

                 [codigo campana contac center] as [codigo campana contac center2],

                 IF(([codigo campana contac center] <>0 and [codigo campana contac center]<> 4) , 'CAMPAÑAS','CNT') AS [PA_PERSONAL_CAMPAÑAS],

                 date(Desde5,'dd-mm-yyyy') as "fec ini campañas",

                 date(Hasta5,'dd-mm-yyyy') as "fec fin campañas"

            FROM

            [Z:\INDICADORES\PROYECTOS\dwh\DEO\052015\DIMENSIONES\cheq personal activo * it.xlsx]

            (ooxml, embedded labels, table is Sheet1)

            WHERE [codigo campana para Contac Cen] <> '';     

            DATA:

            left JOIN ("PA CAMPAÑAS")

            intervalmatch ("Fecha Rol","Nº pers.")

            load DISTINCT "fec ini campañas","fec fin campañas", [Nº pers.] resident "PA CAMPAÑAS";  

             

            PERSONAL:

            add LOAD Distinct [Nº pers.],

                 lookup( 'PA_PERSONAL_CAMPAÑAS', 'Nº pers.', [Nº pers.],'PA CAMPAÑAS') as [PERSONAL CAMPAÑAS 2],

                [Número de personal]  as [Nombre],

                 [F.Ini IT1],

                 [F.Fin IT1]

            FROM

            [Z:\INDICADORES\PROYECTOS\dwh\DEO\052015\DIMENSIONES\cheq personal activo * it.xlsx]

            (ooxml, embedded labels, table is Sheet1);

             

            DATA:

            add inner JOIN ("PERSONAL")

            intervalmatch ("Fecha Rol","Nº pers.")

            load DISTINCT [F.Ini IT1],[F.Fin IT1], [Nº pers.] resident "PERSONAL";

             

            Gracias

             

            John

            • Re: fill the field in a table based on the contents of a field in another table

              Dear Peter,

               

              Previously tried to solve the problem using lookup, the fields that concatenated the 2 tables are: [No. pers.] & [Fecha Rol].

               

              The FECHA ROL field is filled in each table using the intervalmatch, this function is executed after loading the tables.

               

              Is there any way to run APPLYMAP after intervalmatch executed judgment, and so the search index may be the result of concatenating [No. pers.] And [Fecha Rol]?

               

               

              "PA CAMPAÑAS": 

              LOAD  

                   [Nº pers.] AS [Nº pers.] , 

                   [codigo campana contac center] as [codigo campana contac center2],

                   [codigo campana para Contac Cen] as [codigo campana para Contac Cen2] ,

                   IF(([codigo campana contac center] <>0 and [codigo campana contac center]<> 4) , 'CAMPAÑAS','CNT') AS [PA_PERSONAL_CAMPAÑAS],

                   date(Desde5,'dd-mm-yyyy') as "fec ini campañas",

                   date(Hasta5,'dd-mm-yyyy') as "fec fin campañas" 

              FROM

              [Z:\INDICADORES\PROYECTOS\dwh\DEO\052015\DIMENSIONES\cheq personal activo * it.xlsx]

              (ooxml, embedded labels, table is Sheet1)

              WHERE [codigo campana para Contac Cen] <> '';     

               

              DATA:

              left JOIN ("PA CAMPAÑAS")

              intervalmatch ("Fecha Rol","Nº pers.")

              load DISTINCT "fec ini campañas","fec fin campañas", [Nº pers.] resident "PA CAMPAÑAS";  

               

              PERSONAL:

              add LOAD Distinct [Nº pers.],

                   lookup( 'PA_PERSONAL_CAMPAÑAS', 'Nº pers.', [Nº pers.],'PA CAMPAÑAS') as [PERSONAL CAMPAÑAS 2],

                  [Número de personal]  as [Nombre],

                   [F.Ini IT1],

                   [F.Fin IT1],

                   [T.Pos] AS [TIPO POSICION]

              FROM

              [Z:\INDICADORES\PROYECTOS\dwh\DEO\052015\DIMENSIONES\cheq personal activo * it.xlsx]

              (ooxml, embedded labels, table is Sheet1);

               

              DATA:

              add inner JOIN ("PERSONAL")

              intervalmatch ("Fecha Rol","Nº pers.")

              load DISTINCT [F.Ini IT1],[F.Fin IT1], [Nº pers.] resident "PERSONAL";

                • Re: fill the field in a table based on the contents of a field in another table
                  Peter Cammaert

                  I don't understand what you are trying to do. In your code, each INTERVALMATCH LOAD will try to link each value of [Fecha Roi] to an interval as specified by the Start and End values, and match the [N° pers] key value as well. But it looks like your [Fecha Roi] field doesn't exist yet so how do you think this code should work?

                    • Re: fill the field in a table based on the contents of a field in another table

                      Sorry, this is the entire script.

                       

                      In the "U ORGANICA" table, FECHA ROL there are only the end of each month, so when the "PA CAMPAÑAS":  table records are concatenated through intervalmatch, the dates are placed end of month (PA CAMPAÑAS is a  Slowly Changing Dimension)

                       

                      SET ThousandSep=',';

                      SET DecimalSep='.';

                      SET MoneyThousandSep=',';

                      SET MoneyDecimalSep='.';

                      SET MoneyFormat='$ #,##0.00;($ #,##0.00)';

                      SET TimeFormat='h:mm:ss';

                      SET DateFormat='DD/MM/YYYY';

                      SET TimestampFormat='DD/MM/YYYY h:mm:ss[.fff]';

                      SET MonthNames='ene;feb;mar;abr;may;jun;jul;ago;sep;oct;nov;dic';

                      SET DayNames='lun;mar;mié;jue;vie;sáb;dom';

                      //SET Codigo_empl = 48;

                      SET Codigo_empl = 0;

                       

                       

                      fecha:

                      load FECHA as [Fecha Rol],

                           AÑO,

                           SEMESTRE,

                           TRIMESTRE,

                           MES,

                           DIA    

                      FROM

                      Z:\INDICADORES\PROYECTOS\dwh\DATOS\DIMENSIONES\FECHAS.xlsx

                      (ooxml, embedded labels, table is Hoja1);

                       

                      "U. ORGANICA":

                      LOAD [Nº pers.] as [Nº pers.],    

                           [Nombre del empleado o candidato] as [Apellido Nombre],

                           [Períodoen] as [Período En],

                           [SDvPer.] as [Cod.Sub Per],

                           [Texto Subd.per.] as [Sub. Div. Personal],

                            [Ce.coste] as [Cod. C.Costo],

                           [Denominación] as [Centro Costo],

                           [Un.org.] as [Cod.U. Organica],

                           [Txt.breve un.org.] as [U. Organica],

                           MakeDate(Year(DATE(Date#([Períodoen],'DD.MM.YYYY'),'DD/MM/YYYY')),Month(DATE(Date#([Períodoen],'DD.MM.YYYY'),'DD/MM/YYYY')),If(day(DATE(Date#([Fecha pago],'DD.MM.YYYY'),'DD/MM/YYYY'))>27,day(DATE(Date#([Períodoen],'DD.MM.YYYY'),'DD/MM/YYYY')),day(DATE(Date#([Fecha pago],'DD.MM.YYYY'),'DD/MM/YYYY')))) as [Fecha Rol] ,

                           COUNT([Nº pers.]) AS [NRO TRANSACCIONES]

                      FROM

                      [Z:\INDICADORES\PROYECTOS\dwh\DEO\052015\HECHOS\TOTAL CARGAR MES 062015.xlsx]

                      (ooxml, embedded labels, table is Hoja1)

                      WHERE([Nº pers.] = '$(Codigo_empl)' or '$(Codigo_empl)' =0  /*or [Nº pers.] =9262*/) and ([Período En] =[Per.para] or [Per.para]=0)and

                      (([CC-n.]='/0SM' and match(If(day(DATE(Date#([Fecha pago],'DD.MM.YYYY'),'DD/MM/YYYY'))>27,

                           day(DATE(Date#([Períodoen],'DD.MM.YYYY'),'DD/MM/YYYY')),day(DATE(Date#([Fecha pago],'DD.MM.YYYY'),'DD/MM/YYYY'))),15,28,29,30,31))or

                      (NOT match([CC-n.],'/\')

                      and Not match(If(day(DATE(Date#([Fecha pago],'DD.MM.YYYY'),'DD/MM/YYYY'))>27,

                           day(DATE(Date#([Períodoen],'DD.MM.YYYY'),'DD/MM/YYYY')),day(DATE(Date#([Fecha pago],'DD.MM.YYYY'),'DD/MM/YYYY'))),15,28,29,30,31)))

                      GROUP BY [Nº pers.],[Nombre del empleado o candidato],[Períodoen],[SDvPer.],[Texto Subd.per.],[Ce.coste],[Denominación],[Un.org.],[Txt.breve un.org.],

                           MakeDate(Year(DATE(Date#([Períodoen],'DD.MM.YYYY'),'DD/MM/YYYY')),Month(DATE(Date#([Períodoen],'DD.MM.YYYY'),'DD/MM/YYYY')),If(day(DATE(Date#([Fecha pago],'DD.MM.YYYY'),'DD/MM/YYYY'))>27,day(DATE(Date#([Períodoen],'DD.MM.YYYY'),'DD/MM/YYYY')),day(DATE(Date#([Fecha pago],'DD.MM.YYYY'),'DD/MM/YYYY'))))

                      ;

                       

                       

                      "PA CAMPAÑAS": 

                      LOAD  

                           [Nº pers.] AS [Nº pers.] , 

                           [codigo campana contac center] as [codigo campana contac center2],

                           [codigo campana para Contac Cen] as [codigo campana para Contac Cen2] ,

                           IF(([codigo campana contac center] <>0 and [codigo campana contac center]<> 4) , 'CAMPAÑAS','CNT') AS [PA_PERSONAL_CAMPAÑAS],

                           date(Desde5,'dd-mm-yyyy') as "fec ini campañas",

                           date(Hasta5,'dd-mm-yyyy') as "fec fin campañas" 

                      FROM

                      [Z:\INDICADORES\PROYECTOS\dwh\DEO\052015\DIMENSIONES\cheq personal activo * it.xlsx]

                      (ooxml, embedded labels, table is Sheet1)

                      WHERE [codigo campana para Contac Cen] <> '';     

                       

                      DATA:

                      left JOIN ("PA CAMPAÑAS")

                      intervalmatch ("Fecha Rol","Nº pers.")

                      load DISTINCT "fec ini campañas","fec fin campañas", [Nº pers.] resident "PA CAMPAÑAS";  

                       

                      PERSONAL:

                      add LOAD Distinct [Nº pers.],

                           lookup( 'PA_PERSONAL_CAMPAÑAS', 'Nº pers.', [Nº pers.],'PA CAMPAÑAS') as [PERSONAL CAMPAÑAS 2],

                          [Número de personal]  as [Nombre],

                           [F.Ini IT1],

                           [F.Fin IT1],

                           [T.Pos] AS [TIPO POSICION]

                      FROM

                      [Z:\INDICADORES\PROYECTOS\dwh\DEO\052015\DIMENSIONES\cheq personal activo * it.xlsx]

                      (ooxml, embedded labels, table is Sheet1);

                       

                      DATA:

                      add inner JOIN ("PERSONAL")

                      intervalmatch ("Fecha Rol","Nº pers.")

                      load DISTINCT [F.Ini IT1],[F.Fin IT1], [Nº pers.] resident "PERSONAL";

                       

                       

                      thanks,