Hello, I have achieved it in the following way, surely there are better ways, now I have a problem when using intervalmatch, sales outside of working hours do not show them, it is possible to fix this, sale 12/13/2021 20:03: 01 end of work hours 20:00:00
Sales part code, filled every 30 minutes with sales = 0 in case there is none
Ventas_trb:
ODBC CONNECT TO SOFTEXTIL_charone (XUserId is cPSbVZFMWH, XPassword is MUbQEBFGBCZaWUZNeE);
SQL SELECT
Almacen,
Ejercicio,
Numero,
Fecha,
Usuario,
Tipo,
Articulo,
Color,
Talla,
Cantidad,
PVenta,
Dto,
Importe,
PCoste,
Vendedor,
Arqueo,
FecAlta,
FecModi,
Id
FROM "SofTextil_Charone".dbo.E01TPVVentasDT where Ejercicio='2021' and Almacen='21';
vv:
Load
Almacen as al,
Numero as nuu,
Ejercicio as ej,
Articulo as art,
FecAlta as fff,
Id as iidd
Resident Ventas_trb where Numero ='8524' ;// and Almacen='6' and Ejercicio ='2021' ;
Ventas_trb1:
load distinct
Almacen&'-'&right(Ejercicio,2)&'-'&Numero as Num_Doc,
FecAlta as Fec_Timstamp,
Date(Fecha) as Fecha_doc,
num(hour(FecAlta),'00')&':'&num(minute(FecAlta),'00') as Hora,
Importe as Vta,
Id as num_id
Resident Ventas_trb where (Fecha >='02/08/2021' and Fecha <='31/12/2021');
drop table Ventas_trb;
Vtas:
Load
num_id,
Num_Doc,
Fec_Timstamp as Fec_Timstamp,
Fecha_doc as Fecha,
Hora as Hora,
Vta as vta
Resident Ventas_trb1;
drop table Ventas_trb1;
set fecha_prueba_ini = '01/08/2021 09:00:00';
//set fecha_prueba_ini1 = '29/11/2021 13:00:00';
//set fecha_prueba_fin = '29/11/2021 16:30:00';
set fecha_prueba_fin1 = '31/12/2022 23:45:00';
[Data$]:
LOAD
[Code],
Timestamp(Timestamp#([TimeStamp], 'MM.DD.YYYY hh:mm:ss') ) AS [TimeStamp],
[Count(code)],
[TimeStamp1] AS [15min desired],
ceil( frac((Timestamp(Timestamp#([TimeStamp], 'MM.DD.YYYY hh:mm:ss') ))), 1/96) AS [%15min]
FROM
[G:\Descargas\Sample Input for Timestamp (2).xls]
(biff, embedded labels, header is 1 lines, table is Data$);
FOR i=0 to 95
Dimension:
LOAD
ceil( $(i)/96, 1/96) as [%15min],
time(ceil( $(i)/96 +pow(10,-16), 1/96),'hh:mm:ss') as [15min calculated]
AutoGenerate 1;
next
drop table [Data$];
Let vMinDate = '01/08/2021';//Peek('MinDate',-1,'MinMaxDate') - 1;
Let vMaxDate = '31/12/2021';
gg:
Load Date(recno()+'$(vMinDate)') as Date Autogenerate vMaxDate - vMinDate;
//calendar_corte:
left join(gg)
Load
//'$(fecha_prueba)' as fec,
[15min calculated] as tiempo,
//'$(fecha_prueba)'&' '&[15min calculated] as timeee,
0 as vta_h
resident Dimension ;// where Date>= '$(fecha_prueba)';
drop table Dimension;
ii:
load
'999999' as num_id,
'00000' as Num_Doc,
Date as fec,
Date&' '&tiempo as tiems,
hour(tiempo)&':'&minute(tiempo) as tiem,
vta_h as vt_h
resident gg;
drop table gg;
jj:
concatenate(Vtas)
load
num_id,
Num_Doc,
fec as Fecha,
tiems as Fec_Timstamp,
tiem as Hora,
vt_h as vta
resident ii where tiems>= '$(fecha_prueba_ini)' and tiems<= '$(fecha_prueba_fin1)';//(tiems>= '$(fecha_prueba_ini)' and tiems<= '$(fecha_prueba_ini1)') or (tiems>= '$(fecha_prueba_fin)' and tiems<= '$(fecha_prueba_fin1)') order by tiems ASC;
drop table ii;
Part where I prepare the schedule to use intervalmatch
for each fileb in filelist('G:\Descargas\Horarios_*.xls')
////for each fileb in filelist('$(vRutaQVDsTransformed)\Movimientos_h_20190801.QVD') ///para carga de una fecha solo
Historicos_S5_D6:
First $(vArchivosIn) ///3
Load DISTINCT
'$(fileb)' as NombreArchivo,
right(FileBaseName(),8) as Fecha_NombreArchivo
From '$(fileb)'(biff, no labels, table is SEMANA5$);
next fileb;
Ultimos_30_Dias:
first $(vArchivosIn) ///2 //30 //30
NoConcatenate Load Distinct * Resident Historicos_S5_D6;
//WHERE (NUM(Fecha_NombreArchivo) <='$(v1Y)' and NUM(Fecha_NombreArchivo) >='$(v1Y30d)') or (NUM(Fecha_NombreArchivo) >= '$(v30d)') Order By Fecha_NombreArchivo desc;
drop table Historicos_S5_D6;
///// comprobar antes de empezar ciclo que existe la dependienta, si no hay nombre pero si contrato horas nombre = SIN NOMBRE
DROP TABLE FECHA_INI;
FOR q=0 to NoOfRows ('Ultimos_30_Dias')-1
LET vUltimoArchivo = PEEK('NombreArchivo' , q, 'Ultimos_30_Dias');
Let vnum = q+1;
Let vSem = '5';
/////////////////////////// COMPROBACIÓN /////////////////////////////////
CONTRATO:
LOAD distinct @28 as Tio_contrato,
if(isnull(@30),0,1) AS Contrato ,
@30 as Horas_Contrato ,
$(q) AS Idc
FROM '$(vUltimoArchivo)' (biff, no labels, table is SEMANA5$) where @28='HORAS SEMANALES CONTRATO' ;
LET vContrato = Peek('Contrato',0,'CONTRATO');
LET vHcontrato = Peek('Horas_Contrato',0,'CONTRATO');
////////drop table CONTRATO;
//////////////////////// CARGA CICLO DE TIENDAS /////////////////////////
TIENDA:
LOAD distinct @15 as Tienda,
if(len(@16=1),num(@16,'0'),if(len(@16=2),num(@16,'00'),num(@16,'000'))) AS Cod_Tiendaa ,
$(q) AS IDD
FROM '$(vUltimoArchivo)' (biff, no labels, table is SEMANA5$) where @15='TIENDA' ;
LET vcodtienda = Peek('Cod_Tiendaa',0,'TIENDA');
//////////////////////// CARGA CICLO DE DEPENDIENTAS /////////////////////////
//Dependienta_1__Semana_1:
Dependienta_6_S5_INI:
LOAD @1 as Tip_Empleado,
@28 as Nom_Empleado,
$(q) AS IDDD
FROM '$(vUltimoArchivo)' (biff, no labels, table is SEMANA5$) where @1='DEPENDIENTA';
LET Dep_6_S5 = Peek('Nom_Empleado',0,'Dependienta_6_S5_INI');
//////////////////////// CARGA CICLO DE FECHAS /////////////////////////
/// El valor de id2 = fecha inicio de cada semana debe crearse aquí
//// POSIBLE CICLO PARA CALENDARIO POR SEMANAS ///
//Fecha_Semana_1:
FECHA_INI:
LOAD @1 as Tip_Empleado,
// @2,
DATE(@3,'DD/MM/YYYY') as FECHA,
//num(@3)&'_'&num(@16) as cod_id,
num(@3) as cod_id
FROM '$(vUltimoArchivo)' (biff, no labels, table is SEMANA5$) where @1='SEMANA DEL' ;
LET id56 = Peek('cod_id',$(q),'FECHA_INI');
/***************************************************************************************************************************************20220111*/
Horario_trb1: //Ultimos_30_Dias;
LOAD Distinct //*
@1 as dia_semana,
RecNo() as numero_linea, //RecNo()&'_'&'$(q)' as numero_linea,
@27 as tipo_de_dia,
if(isnull(@28),'',@28) as H_in,//@3,
if(isnull(@29),'',@29) as H_ou//,//@4,
FROM '$(vUltimoArchivo)' (biff, no labels, table is SEMANA5$);
/***************************************20220111*/
Horario_trb2:
Load
if(dia_semana='Lunes', date('$(id56)','DD/MM/YYYY'),if(dia_semana='Martes',date('$(id56)'+1,'DD/MM/YYYY'),if(dia_semana='Miércoles',date('$(id56)'+2,'DD/MM/YYYY'),if(dia_semana='Jueves',date('$(id56)'+3,'DD/MM/YYYY'),if(dia_semana='Viernes',date('$(id56)'+4,'DD/MM/YYYY'),if(dia_semana='Sábado',date('$(id56)'+5,'DD/MM/YYYY'),if(dia_semana='Domingo',date('$(id56)'+6,'DD/MM/YYYY')))))))) as ffees,
if(not isnull( tipo_de_dia), 'Mañana', 'Tarde') AS tipo,
if(isnull( dia_semana),Previous(dia_semana),dia_semana) as dia_semanas,
$(vSemana5)&$(vDependienta6)&$(vnum)&$(vSem)&'_'&numero_linea as numero_lineas,
if(isnull( tipo_de_dia), previous(tipo_de_dia), tipo_de_dia) AS tipo_de_diass,
H_in as H_ins,
H_ou as H_ous
Resident Horario_trb1 where (numero_linea > 6 and numero_linea<=20) and LEFT(dia_semana,5)<>'TOTAL';
drop table Horario_trb1;
/***************************************20220111*/
Horario_trb:
Load
if(isnull( ffees), previous(ffees),ffees) as ffee,
tipo as tipos,
dia_semanas as dia_semana,
numero_lineas as numero_linea,
tipo_de_diass AS tipo_de_dias,
H_ins as H_in,
H_ous as H_ou
Resident Horario_trb2 ;
drop table Horario_trb2;
/***************************************20220111*/
P1_56:
LOAD
ffee as fec,
tipos as tiposs,
'$(vcodtienda)' as Cod_Tie_D1_S2,
'$(Dep_6_S5)' as Depend_D1_S2,
'$(vHcontrato)' as HContrato,
dia_semana as dia_semanaFF,
numero_linea as numero_lineaFF,
tipo_de_dias AS tipo_de_diasFF,
//'O' as Uno,
H_in as H_inFF,
H_ou as H_ouFF
RESIDENT Horario_trb order by numero_linea ASC;// WHERE tipo_de_dias<>'F';
/***************************************20220111*/
CONCATENATE(P1_56)
LOAD
date(date#(ffee,'D/M/YYYY'),'DD/MM/YYYY') as fec,
tipos as tiposs,
'$(vcodtienda)' as Cod_Tie_D1_S2,
'$(Dep_6_S5)' as Depend_D1_S2,
'$(vHcontrato)' as HContrato,
dia_semana as dia_semanaFF,
numero_linea as numero_lineaFF,
tipo_de_dias AS tipo_de_diasFF,
//'A' as Uno,
H_in as H_inFF,
H_ou as H_ouFF
RESIDENT Horario_trb WHERE tipo_de_dias='F' or tipo_de_dias='V' or tipo_de_dias='f' or tipo_de_dias='v';
drop table Horario_trb;
drop table Dependienta_6_S5_INI;//,TIENDA ;
/***************************************20220111*/
next
DROP Table Ultimos_30_Dias;
/****************************************************************************************************************20220111*/
Horario_trb20:
noconcatenate load
fec as Dia_S,
tiposs as Tipo_D1_S2,
recno() as id,
Cod_Tie_D1_S2 as Cod_Tie_D1_S2,
Depend_D1_S2 as Depend_D1_S2,
HContrato as HContrato,
dia_semanaFF as dia_semana_f,
numero_lineaFF as numero_linea_f,
tipo_de_diasFF AS tipo_de_dias_f,
//Uno as Uno1,
H_inFF as H_in_f,
H_ouFF as H_ou_f
resident P1_56 order by numero_lineaFF ASC;
drop table P1_56;
/************************************************************************************20220111*/
Salida5ou6:
Load
//if(tipo_de_dias_f='F','Festivo',if(tipo_de_dias_f='f','Festivo',if(tipo_de_dias_f='v','Vacaciones',if(tipo_de_dias_f='V','Vacaciones','Laborable')))) as Tipo_dia_trabajo,
HContrato as HContratoS56,
Cod_Tie_D1_S2 as Cod_TieS56, //if(IsNull(H_in_f) or H_in_f='','',Cod_Tie_D1_S2) as Cod_Tie5,
Tipo_D1_S2 as TipoS56, //if((IsNull(H_in_f) or Tipo_D1_S2='') or (IsNull(H_ou_f) or H_ou_f=''),'',Tipo_D1_S2) as Tipo5,
Depend_D1_S2 as DependS56, //if(IsNull(H_in_f) or H_in_f='','',Depend_D1_S2) as Depend5, //Depend as Depend,
Dia_S as Fecha2S56, //if(IsNull(H_in_f) or H_in_f='','',Dia_S) as Fecha25, //
if(IsNull(H_in_f) or H_in_f='','', Dia_S&' '&date(H_in_f,'hh:mm:ss')) as horario_inS56, //if(IsNull(LIN) or LIN='','', '$(Fecha_s1)'&' '&date(LIN,'hh:mm:ss')) as horario_in, '$(Fecha_s1)'&' '&date#(IN_1,'hh:mm:ss') as horario_in,
if(IsNull(H_ou_f) or H_ou_f='','', Dia_S&' '&date(H_ou_f-maketime(0,1,0),'hh:mm:ss')) as horario_ouS56 //+maketime(0,1,0)//if(IsNull(LOUT) or LOUT='','', '$(Fecha_s1)'&' '&date(LOUT,'hh:mm:ss')) as horario_ou '$(Fecha_s1)'&' '&date#(OUT_1,'hh:mm:ss') as horario_ou
//date#(date1&' '&from_time,'DD/MM/YYYY hh:mm:ss') as "FromTime1",
//date#(date1&' '&to_time,'DD/MM/YYYY hh:mm:ss') as "ToTime1"
Resident Horario_trb20;
drop table Horario_trb20;
/************************************************************************************20220111*/
if $(vContrato)=0 then
else
concatenate(Salida22) //Salida22:
Load distinct
//if(tipo_de_dias_f='F','Festivo',if(tipo_de_dias_f='f','Festivo',if(tipo_de_dias_f='v','Vacaciones',if(tipo_de_dias_f='V','Vacaciones','Laborable')))) as Tipo_dia_trabajo,
if(horario_inS56='','',HContratoS56) as HContrato,
if(horario_inS56='','',Cod_TieS56) as Cod_Tie, //if(IsNull(H_in_f) or H_in_f='','',Cod_Tie) as Cod_Tie,
if(horario_inS56='','',TipoS56) as Tipo, //if((IsNull(H_in_f) or Tipo='') or (IsNull(H_ou_f) or H_ou_f=''),'',Tipo) as Tipo,
if(horario_inS56='','',DependS56) as Depend, //if(IsNull(H_in_f) or H_in_f='','',Depend) as Depend, //Depend as Depend,
if(horario_inS56='','',Fecha2S56) as Fecha2, //if(IsNull(H_in_f) or H_in_f='','',Dia_S) as Fecha2, //
horario_inS56 as horario_in, //if(IsNull(LIN) or LIN='','', '$(Fecha_s1)'&' '&date(LIN,'hh:mm:ss')) as horario_in, '$(Fecha_s1)'&' '&date#(IN_1,'hh:mm:ss') as horario_in,
horario_ouS56 as horario_ou //+maketime(0,1,0)//if(IsNull(LOUT) or LOUT='','', '$(Fecha_s1)'&' '&date(LOUT,'hh:mm:ss')) as horario_ou '$(Fecha_s1)'&' '&date#(OUT_1,'hh:mm:ss') as horario_ou
//date#(date1&' '&from_time,'DD/MM/YYYY hh:mm:ss') as "FromTime1",
//date#(date1&' '&to_time,'DD/MM/YYYY hh:mm:ss') as "ToTime1"
Resident Salida5ou6 where horario_inS56>0 order by Fecha2S56 ASC;
endif;
drop table Salida5ou6;
/*
pruebavtas:
load
num_id as num_id22,
Num_Doc as Num_Doc22,
Fecha as Fecha22,
if( Fec_Timstamp<horario_in,min(horario_in),if(Fec_Timstamp>horario_ou,max(horario_ou),Fec_Timstamp)) as esta,
Hora as Horass,
vta as vtass
resident Vtas where Num_Doc='8524';
*/
/***************************************************************************************************************************************20220111*/
Resultado:
load vta, Hora, Num_Doc, num_id, Fec_Timstamp as Fec_Timstamp1 // date#(Fec_Timstamp,'DD/MM/YYYY hh:mm:ss') as Hora_vta
Resident Vtas;
/***************************************************************************************************************************************20220113*/
IntervalMatch (Fec_Timstamp1)
load horario_in, horario_ou resident Salida22;
drop table Vtas;
/**********************************************20220111*/
///// CALENDARIO
MasterCalendar:
LOAD distinct
//'$(id2)' as id1,
Fecha2 as Fecha2,
//'Mañana' as Tipo,
//Week(TempDate) as Semana,
Year(Fecha2) as Año,
Month(Fecha2) as Mes,
Day(Fecha2) as Dia,
//Weekday(TempDate) as WeekDay,
//'Q' & ceil(month(TempDate) / 3) as Trimestre,
//'Q' & Ceil(Month(TempDate)/3) & '-' & Year(TempDate) as Trimestre_Año,
//MonthName(TempDate) as Mes_Año,
Week(Fecha2)&'-'&Year(Fecha2) as Semana_Año
Resident Salida22
Order By Fecha2 ASC;
Thanks for the help
