Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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?

5 Replies
Peter_Cammaert
Partner - Champion III
Partner - Champion III

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.

Not applicable
Author

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

(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

(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

Not applicable
Author

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

(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

(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";

Peter_Cammaert
Partner - Champion III
Partner - Champion III

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?

Not applicable
Author

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

(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

(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

(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,