comparison date field is blank in qlik incremental load

[deep2021]

Hi All,

In the incremental load for the insert logic my date field is showing as a blank.

Below is the piece of code for this.

$(IncrTimestampAttr) >=to_date('$(vL.Last_Exec_$(TableName))', 'YYYY-MM-DD HH:MI:SS')
AND
$(IncrTimestampAttr) <to_date('$(vL.This_Exec_$(TableName))', 'YYYY-MM-DD HH:MI:SS')

I am passing update date to the variable IncrTimestampAttr. 

Do i need to use peek to retrieve for the max value for the variable IncrTimestampAttr.

Can you please suggest in this.

Thanks,

[kaushiknsolanki]

IncrTimestampAttr Has to be a field name and not the date value.

[deep2021]

Hi, 

PFB screenshot. We are passing the column in the variable.

We have maintained all values in excel.

Thanks

[kaushiknsolanki]

Share your script for better understanding. 

[deep2021]

Hi,

PFB code for the same.

/*----------------------------------Include Container Variable DEFs---------------------------------------*/
//$(Must_Include=$(vHome_Container)3.Include/2.Custom/Container_Variable_Def.qvs);
/*--------------------------------------------------------------------------------------------------------*/
/*-----------------------------Variables Specific to QVD Extractor ONLY-----------------------------------*/
//Database Connection to RPR ORACLE DB
//LIB CONNECT TO 'KYC-RPR-DB';
/*--------------------------------------------------------------------------------------------------------*/
//vActiveFlag = 1 for RPR tables
SET vActiveFlag = 1; //DEFINE THIS BEFORE CALLING THE QVD GEN SCRIPT TO MAKE IT DYNAMIC

//SET vConfig_Files = '';
//SET vQVD_Extract = '';
//SET vQVD_Metadata = '';

//[lib://DELVE00077 (dbg_gandhara-a)/Kinesis/Data/QVD/Layer1/UAT/A_MTH_RETURN_FACTOR_VIEW_11072020.qvd](qvd);

SUB StoreQVD
//QVD Storage
IF NoOfRows('$(TableName)')>0 then
TRACE Generating the QVD $(TableName)...;
STORE [$(TableName)] into [$(vQVD_Extract)$(QVDName).QVD];
TRACE '----------$(vQVD_Extract)$(QVDName).QVD Generation has successfully been Completed------------';
ELSE
STORE [$(TableName)] into [$(vQVD_Extract)$(QVDName).QVD];
TRACE Source Table is Empty. EMPTY QVD is Generated;
//TRACE Source Table is Empty QVD is not Generated;
END IF

//QVD DETAILS info
LET vFile = '$(vQVD_Extract)$(QVDName).QVD';
LET vL.after=now();
QVDDETAILS:
LOAD
'$(QVDName)' AS QVD_Name,
'$(TableName)' AS Source_Table_Name,
Replace('$(vEntityType)','.','') AS Source,
'$(vQVD_Extract)' AS QVD_Location,
fiLETime('$(vQVD_Extract)$(QVDName).QVD') AS QVD_Creation_timestamp,
num(filesize('$(vQVD_Extract)$(QVDName).QVD')/(1024*1024),'##,###.00')
AS [QVD_Size(MB)],
qvdnoofrecords('$(vQVD_Extract)$(QVDName).QVD') AS QVD_Record_Count,
qvdnooffields('$(vQVD_Extract)$(QVDName).QVD') AS QVD_Field_Count,
time(Timestamp('$(vL.after)') - timestamp('$(vL.before)'),'hh:mm:ss')
AS QVD_Reload_Time,
'$(vL.This_Exec_$(TableName))' AS QVD_Time_This_Exec,
'$(vL.Last_Exec_$(TableName))' AS QVD_Time_Last_Exec,
'$(vIncr_Timestamp_Attr)' AS QVD_Incr_Timestamp_Attr,
'$(vIncr_Primary_Key)' AS QVD_Incr_Primary_Key,
if('$(vLoad_Type_Input)' = 0,'FULL','INCREMENTAL')
AS Load_Type_Input,
'$(vL.Load_Type)' AS Load_Type_Actual
AutoGenerate 1 ;
drop table [$(TableName)];

LET vDocumentName = If(wildmatch(DocumentTitle(), '*(*'),
left(DocumentTitle(),index(DocumentTitle(),'(')-1),
DocumentTitle()); //Remove any Parenthesis due to app duplications
if filesize('$(vQVD_Metadata)QVDGen_Metadata_$(vDocumentName).qvd') > 0 and not isnull( filesize('$(vQVD_Metadata)QVDGen_Metadata_$(vDocumentName).qvd')) then
Concatenate(QVDDETAILS)
LOAD DISTINCT
QVD_Name,
Source_Table_Name,
Source,
QVD_Location,
QVD_Creation_timestamp,
[QVD_Size(MB)],
QVD_Record_Count,
QVD_Field_Count,
QVD_Reload_Time,
QVD_Time_This_Exec,
QVD_Time_Last_Exec,
QVD_Incr_Timestamp_Attr,
QVD_Incr_Primary_Key,
Load_Type_Input,
Load_Type_Actual
FROM [$(vQVD_Metadata)QVDGen_Metadata_$(vDocumentName).qvd] (qvd)
;
end if

STORE QVDDETAILS INTO [$(vQVD_Metadata)QVDGen_Metadata_$(vDocumentName).qvd] (qvd);
LET vL.Last_Exec_$(TableName) = vL.This_Exec_$(TableName); //End of Execution Set After = Before timestamp for next incremental load
END SUB
;

SUB GenerateQVD(TableName,Columns,EntityType,WhereCond,Load_Parm,QVDName,IncrTimestampAttr,IncrPrimKey,LoadTypeInput)
LET vL.before=now();
LET vL.This_Exec_$(TableName) = timestamp(now(1),'YYYY-MM-DD hh:mm:ss');
//******FORCE FULL LOAD ON FEW DAYS AND LIMITED TIME FRAME **********************
LET vL.Full_Load_Time =
(Interval#(Time(now(1),'hh:mm'),'hh:mm') <= Interval('02:10','hh:mm')
and
Interval#(Time(now(1),'hh:mm'),'hh:mm') >= Interval('01:30','hh:mm'))
and
wildmatch(Weekday(now()),'*FRI*','*SAT*','*SUN*')
;
//*******************************************************************************
//LET vL.Load_Type = 'INCREMENTAL';
IF wildmatch('$(vEntityType)','*DB*','*SQL*','*ORACLE*','*SYBASE*','*ACCESS*') THEN
IF (ISNULL(QvdCreateTime('$(vQVD_Extract)$(QVDName).QVD')) OR (QvdNoOfRecords ('$(vQVD_Extract)$(QVDName).QVD') = 0) OR ($(LoadTypeInput) = 0)) OR $(vL.Full_Load_Time) THEN
[$(TableName)]:
SQL SELECT
$(Columns)
FROM $(TableName)
Where $(WhereCond)
;
LET vL.Load_Type = 'FULL';
ELSE
[$(TableName)]:
SQL SELECT
$(Columns)
FROM $(TableName)
where
$(WhereCond)
AND
// $(IncrTimestampAttr) >= TO_DATE('$(vL.Last_Exec_$(TableName))')
// AND
// $(IncrTimestampAttr) < TO_DATE('$(vL.This_Exec_$(TableName))')
$(IncrTimestampAttr) >=to_date('$(vL.Last_Exec_$(TableName))', 'YYYY-MM-DD HH:MI:SS')
AND
$(IncrTimestampAttr) <to_date('$(vL.This_Exec_$(TableName))', 'YYYY-MM-DD HH:MI:SS')
;
EXIT SCRIPT;
//*;


// to_date('$(vL.Last_Exec_$(TableName))', 'YYYY-MM-DD HH:MI:SS'); conversion
// TO_DATE('$(vL.Last_Exec_$(TableName))','YY-MM-DD hh24:mi:ss')
Concatenate
LOAD *
FROM [$(vQVD_Extract)$(QVDName).QVD](qvd)
WHERE NOT EXISTS($(IncrPrimKey));
;
Inner Join SQL SELECT $(IncrPrimKey) FROM $(TableName);
LET vL.Load_Type = 'INCREMENTAL';
END IF
TRACE '----The Load was $(vL.Load_Type) Load-----';
TRACE '----------$(TableName)Found in the source; data loaded with $(vL.Load_Type) Load------';
//QVD Storage
CALL StoreQVD;
ELSE
IF alt(Filesize('$(vSource_Data)$(TableName).$(vEntityType)'),0) = 0 THEN
TRACE $(vSource_Data)$(TableName).$(vEntityType)...Not Found in the source - Extraction FAILED;
ELSE
[$(TableName)]:
LOAD
$(Columns)
//,'$(vL.before)' AS $(QVDName)_Timestamp
FROM
[$(vSource_Data)$(TableName).$(vEntityType)]($(Load_Parm));
CALL StoreQVD;
END IF
END IF
End SUB;

//This info is just to understand the Config Files Details.
Tables:
LOAD [Active Flag],
[Entity Name],
[Source Entity Type],
[Where Cond],
[Load Parm],
[QVD Table Name] AS QVD_Name,
[Incr_Timestamp_Attr],
[Incr_Primary_Key],
[Load_Type_Input] AS Load_Input
FROM
[$(vConfig_Files)RPR_Data_Extract_Config.xlsx]
(ooxml, embedded labels, table is Tables_Incr)
where [Active Flag] = $(vActiveFlag) //Control Tables to load through Active Flag variable
;

// FROM [lib://DELVE00077 (dbg_gandhara-a)/Kinesis/Data/QVD/Layer3/UAT/Excel/RPR_Data_Extract_Config.xlsx]
// (ooxml, embedded labels, table is Tables_Incr);

Inner Join(Tables)
LOAD [Entity Name],
[Columns To Load]
FROM
[$(vConfig_Files)RPR_Data_Extract_Config.xlsx]
(ooxml, embedded labels, table is Columns_Incr)
where [Active Flag] = $(vActiveFlag) //Control Tables to load through Active Flag variable
;

FOR vRow = 0 TO NoOfRows('Tables') - 1

IF peek('Active Flag', vRow, 'Tables') = $(vActiveFlag) THEN

LET vTable = peek('Entity Name', vRow, 'Tables');
LET vColumns = peek('Columns To Load', vRow, 'Tables');
LET vEntityType = peek('Source Entity Type', vRow, 'Tables');
LET vWhereCond = peek('Where Cond', vRow, 'Tables');
LET vLoad_Parm = peek('Load Parm', vRow, 'Tables');
LET vQVD = peek('QVD_Name', vRow, 'Tables');
/* Attributes Specific For Incremental Load */
LET vIncr_Timestamp_Attr = peek('Incr_Timestamp_Attr', vRow, 'Tables');
LET vIncr_Primary_Key = peek('Incr_Primary_Key', vRow, 'Tables');
LET vLoad_Type_Input = peek('Load_Input', vRow, 'Tables');
call GenerateQVD(vTable,vColumns,vEntityType,vWhereCond,vLoad_Parm,vQVD,vIncr_Timestamp_Attr,vIncr_Primary_Key,vLoad_Type_Input);

END IF
NEXT

//Drop table Tables;
LET vTable =;
LET vColumns=;
LET vEntityType =;
LET vLoad_Parm =;
LET vQVD =;
LET vRow =;
LET vFile=;
/* LET vQVD_Extract=;
LET vSource_Data =;
*/

//DROP TABLES QVDDETAILS,Tables,Tables_Incr;

We are using the columns that are created from the excel.

Thanks

[deep2021]

Hi,

PFA.

I have attached the sample code.

We are using the same excel structure that are mentioned in the conversation.

our  UPDATE_DATE  's format is 2021-01-23 13:10:23.000000.

Can you please suggest the possible way to compare the dates with the above format with the oracle db date for the incremental load.

Thanks

[edwin]

did you check (for one iteration) if the variable evaluated for 

vL.This_Exec_$(TableName)

has a value and is it in the rt format?

[deep2021]

Hi Edwin,

The format is right. The problem is with Oracle date converiin. Whenever I am using to_char it is removing trailing 0 and . From the string.

But after using to_date the same string reappears.

So the comparison is failed.

Thanks

[marcus_sommer]

If I understand it right the Qlik variables does exists and have also the right values and formats. The issues are occurring by the conversion/formatting with the oracle-functions. In this case you will be get better hints and examples by any of the oracle communities.

Beside this I suggest to consider if you could change for the comparison the variables and the appropriate oracle fields to purely numeric values.

It's in general recommended to make all kind of calculations and matchings with pure numeric values to avoid all the conversion/formatting trouble and even it mean that some fields needs to be loaded twice within the data it may be more performant because all the conversion/formatting stuff will also have some performance efforts.

- Marcus

[Michael_Tarallo]

Hi Deep - I got your PM - I know @kaushiknsolanki Kaushik very well - he should be able to help you. He's one of our best and brightest!