Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! Learn More
vandrecarlos
New Contributor III

Script - Date on Dimension

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!