See best criteria for loading qlikview script

[roberto99]

Hi, I have a sales table, which I group by companies ( T1,T3,T4 => Emp 1 ) (T2,T5 => Emp 2) ,

however I need to know the most efficient way for the case in which from the day 02/02/2021 T3 stop being in Emp 1 and happen to be in Emp 2.

Currently in the stores table I do a mapping to add the store and company to the sales table, but that does not work for me in the case of a change
of company since from the date indicated the previous sales of T3 would happen to be in Emp 2 and that is not correct.

Thanks for the help

[JuanGerardo]

Hi @roberto99, you need to include the date in your criteria. Depending on how you are doing this now (mapping, join, etc.) it could vary, but you need to compare Tx + Date to assign the correct Emp.

JG

[roberto99]

Thanks, I do a Mapping to add Emp x to table ventas

Table ventas source Fecha, Importe , Tienda
Table tienda source Tienda , Emp

However, I do not have where to get a change date in the table source, can you give me an idea of how to do it?

Ventas:
Load
Cod_Tienda AS tienda, (Tx)
Tipocont_ as emp  (emp x)
sum(Importe_) as Importe
Resident Ventas_trb group by Cod_Tienda, emp;
drop table Ventas_trb;

Maybe I can get the cutoff dates from another table that contains emp, tienda and daily dates, but I don't know how to create a table where to see the tiendas that change companies with their change date.

Example:

fecha              tienda       emp         valor
01/02/2021      2                2                3
01/02/2021      3                1                5
02/02/2021      3                2               8
02/02/2021      2                2               9

I imagine that with peek I could but I don't know how, here is an example but there are more tiendas and at least 5 changes per year in different tiendas.

Thank you for answering so quickly

[JuanGerardo · Accepted solution]

Hola @roberto99,

What about doing a mapping with both fields? Like this:

MAP_Emp:

Mapping Load

fecha & tienda as ID,

emp

Resident DailyTable;

Ventas:

Load

...

ApplyMap('MAP_Emp', fechaventa & Cod_Tienda) AS Empresa

...

JG

[roberto99]

Forgive me but I can't understand the example you set for me, where does it come from "fechaventa" ?

[roberto99]

These are the two parts I work with now

Mapping_Tiendas:
Mapping Load
Codigo AS Cod_Tienda,
Tipo_Tienda as Tipo_Tienda
Resident Tiendas_qv;

Ventas_trb1:
Load
Codigo as Codigo_, // esto es nuevo a añadir luego en la otra tabla
ApplyMap('Mapping_Tiendas', Codigo,'0') As tipocont_,
Ejercicio as Ejercicio_1,
Fecha as Fecha_1,
Id as id_,
Importe as Importe_1
Resident Ventas_qv ;//where Fecha>='$(vIniTrimestreUno)' and Fecha<='$(vFinTrimestreUno)';

[JuanGerardo]

I didn't know the date field in your Ventas table, so I put fechaventa, but it seems it is called Fecha in your Rentas_qv table.

If you want your mapping to depend on the date, you need to include the date in the mapping and in the ventas table where you use the mapping. You need to indicate the Codigo and the date where to check the Tienda corresponding to that Codigo at that date. In my example, I did that concatenating both fields.

JG

[roberto99]

Thank you very much, I had it right in front of me and I couldn't see it