Morning, guys! I'm having an issue on my script, because I dont know how to put days on my analysis. Here's the code.
===================================================================================================
LET vBefore = Now();
[AUX_A905]:
LOAD
KSCHL,
MATNR,
DATE(DATAB,'DD/MM/YYYY') AS [DT_INICIO A905],
YEAR(DATAB) AS [ANO_INICIO A905],
NUM(MONTH(DATAB),'00') AS [MES_INICIO A905],
DATE(DATBI,'DD/MM/YYYY') AS [DT_FIM A905],
YEAR(DATBI) AS [ANO_FIM A905],
NUM(MONTH(DATBI),'00') AS [MES_FIM A905],
APPLYMAP('KONP', KNUMH & '\' & KSCHL) AS [VLR_META A905]
FROM [lib://EXTR_METAS (qliksense_administrator)/A905.qvd](qvd)
WHERE KSCHL = 'ZCAA'
AND YEAR(DATAB) >= YEAR(NOW())-1;
//*******************************************************************************************************************************
//** CRIAÇÃO DA DIMENSÃO DIMGR_META_MATERIAL
//*******************************************************************************************************************************
// Nome da Tabela
LET vTableName = 'DIMGR_META_MATERIAL';
LET vLINHAS_AUX = NoOfRows('AUX_A905');
FOR n=1 TO $(vLINHAS_AUX)
LET vANOINICIAL = PEEK('ANO_INICIO A905',n-1,'AUX_A905');
LET vANOFINAL = PEEK('ANO_FIM A905',n-1,'AUX_A905');
LET vKSCHL = PEEK('KSCHL', n-1,'AUX_A905');
LET vMATNR = PEEK('MATNR', n-1,'AUX_A905');
LET vVALOR = PEEK('VLR_META A905', n-1,'AUX_A905');
IF $(vANOFINAL) = '9999' THEN
vANOFINAL = YEAR(NOW());
END IF;
FOR ano=$(vANOINICIAL) TO $(vANOFINAL)
LET vMESINICIAL = PEEK('MES_INICIO A905',n-1,'AUX_A905');
LET vMESFINAL = PEEK('MES_FIM A905',n-1,'AUX_A905');
IF $(vANOINICIAL) <> $(vANOFINAL) THEN
IF $(ano) > $(vANOINICIAL) THEN
vMESINICIAL = '01';
END IF;
END IF;
IF $(vANOINICIAL) <> $(vANOFINAL) THEN
IF $(ano) < $(vANOFINAL) THEN
vMESFINAL = '12';
END IF;
END IF;
FOR mes=$(vMESINICIAL) TO $(vMESFINAL)
LET vDTMETA = MONTHEND('01/'& $(mes) & '/' & $(ano));
[$(vTableName)]:
LOAD
'$(vMATNR)' & '\' &
MONTHNAME('$(vDTMETA)') AS [PK_META_MATERIAL A905],
'$(vKSCHL)' AS [TIPO_CONDICAO A905],
'$(vMATNR)' AS [MATERIAL A905],
'$(vDTMETA)' AS [DT_META A905],
'$(vVALOR)' AS [CAMPANHA_MATERIAL A905],
'1' AS [VLR_META A905]
AUTOGENERATE 1;
NEXT mes
NEXT ano
NEXT n
DROP TABLE [AUX_A905];
==================================================================================================
As you guys can see, the code is only considering month and year for the fields DATBI and DATAB.
I need to put the DAY on the code so I can extract measures from the table acording to the exact date, because I have some materials that have just few days with the tag on and not the whole month or year.
Can you guys help me? 'Cuz I dont know how to put the day on it and make it work.
Thanks!
