Script - Date on Dimension

vandrecarlos — Thu, 21 Mar 2019 13:00:10 GMT

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!

topic Script - Date on Dimension in App Development

Script - Date on Dimension