Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am doing an incremental load of a FileDate(date value) and FileTimeStamp(hh:mm value), but it is only keeping the 2 most recent entries and is erasing the oldest ones. This will be a great value for me to be able to control so my qvd doesnt grow, but I need to find out what that part of the Incremental Setup and Incremental Update is so I can adjust it.
Incremental Setup
//Manual Recon Timeliness incremental
SET MRTPKexp=autonumberhash128(FileDate);
LET MRTtable='ManReconTime';
If FileSize('$(MRTQVDFILE)')>0 THEN
SET MRTQVD_EXISTS=1;
ELSE
SET MRTQVD_EXISTS=0;
END if
IF $(MRTQVD_EXISTS) THEN
maxdateTab:
LOAD max(FileDate)
From $(MRTQVDFILE)(qvd);
LET INCREMENTAL_EXP='WHERE FileDate>='&max('FileDate',1);
DROP table maxdateTab;
ELSE
LET INCREMENTAL_EXP='';
END IF
Incremental Update
//Manual Recon Completeness incremental
Directory;
IF $(MRCQVD_EXISTS) THEN
RIGHT JOIN ($(MRCtable)) LOAD DISTINCT
*
,$(MRCPKexp) as MRCPK
RESIDENT $(MRCtable)
;
CONCATENATE ($(MRCtable)) LOAD * FROM $(MRCQVDFILE) (qvd)
WHERE NOT exists(MRCPK, $(MRCPKexp))
;
DROP FIELD MRCPK;
END IF
Date Load
$(MRTtable):
LOAD FileDate,
Date(FileDate,'MM/DD/YYYY'),
FileTimeStamp,
Timestamp(FileTimeStamp,'hh:mm')
FROM
[..\..\MEC Snapshot\Data\Manual_Recon_Flexi_Upload.xlsx]
(ooxml, embedded labels, table is Matched, filters(
Remove(Col, Pos(Top, 9)),
Remove(Col, Pos(Top, 8)),
Remove(Col, Pos(Top, 7)),
Remove(Col, Pos(Top, 6)),
Remove(Col, Pos(Top, 5)),
Remove(Col, Pos(Top, 4)),
Remove(Col, Pos(Top, 3)),
Remove(Col, Pos(Top, 2)),
Remove(Col, Pos(Top, 1)),
Remove(Col, Pos(Top, 7)),
Remove(Col, Pos(Top, 6)),
Remove(Col, Pos(Top, 5)),
Remove(Col, Pos(Top, 4)),
Remove(Col, Pos(Top, 3)),
Remove(Col, Pos(Top, 2)),
Remove(Col, Pos(Top, 1)),
Remove(Col, Pos(Top, 4)),
Remove(Col, Pos(Top, 3)),
Remove(Col, Pos(Top, 2)),
Remove(Col, Pos(Top, 1)),
Remove(Row, RowCnd(Interval, Pos(Top, 3), Pos(Bottom, 1), Select(0, 0)))
));
As always, any help is much appreciated!
Dan
Hi,
I would suggest to keep the From_Date and To_Date in Table in Database and use select query to fetch value and hold in Variable in QlikView Script and use same for loading data incrementally.
you can schedule a Upadate query to Update your From_Date and To_Date each day before running the QlikView Job.
//DECLARE VARIABLE FROM_DATE_KEY & TO_DATE_KEY AS INT
SET V_FROM_DATE_KEY='INT';
SET V_TO_DATE_KEY='INT';
/*-----------------------------------------------------------------************---------------------------------------------------------------*/
//GET FROM & TO DATE FROM QLIKVIEW CONFIGURATION TABLE
QLIKVIEW_QVD_CONFIG:
SQL
SELECT
CONVERT(VARCHAR(10),[EXTRACT_FROM_DT],112) AS FROM_DT_KEY,
CONVERT(VARCHAR(10),[EXTRACT_TO_DT],112) AS TO_DT_KEY
FROM [QLIKVIEW].[QLIKVIEW_QVD_Config]
WHERE [TABLE_NAME]='STAR.FACT_TABLE';
//GET FROM & TO DATE FROM QLIKVIEW CONFIGURATION TABLE
/*-----------------------------------------------------------------************---------------------------------------------------------------*/
//ASSIGNE THE VALUES TO VARIABLES FROM_DATE_KEY & TO_DATE_KEY
LET V_FROM_DATE_KEY =Peek('FROM_DT_KEY',0,'QLIKVIEW_QVD_CONFIG');
LET V_TO_DATE_KEY =Peek('TO_DT_KEY',0,'QLIKVIEW_QVD_CONFIG');
DROP TABLE QLIKVIEW_QVD_CONFIG;
SELECT * FROM STAR.FACT_TABLE WHERE (DATE_KEY BETWEEN $(V_FROM_DATE_KEY) AND $(V_TO_DATE_KEY))
One time you have insert data into table [QLIKVIEW].[QLIKVIEW_QVD_Config] and keep updating EXTRACT_FROM_DT and EXTRACT_TO_DT before loading qlikview appliaction.