Using DATE TIME functions in Qlik Replicate to translate into date and time

[adbdkb]

I am trying to convert incoming data from DB2 as source into Kafka as Target.

The dates in DB2 are coming in as Numeric (8,0) in the Task when I select the tables

They are defined as numeric with values as YYYYMMDD or 99999999

I want to define the function mapping using one of the date time functions to translate the inputs coming in as 20220525 or 99999999 into valid date format.  I couldn't find an example of what the modifier should be if using these functions

Thank you

[Michael_Litz · Accepted solution]

Hi adbdkb,

PREVIEW

The replicate expression language is based on SQLite syntax which you can google for additional reference.

I think that you will need to use this type of function to format the date.
strftime('%Y %m %d','now');

 This example used the 'now' which is a date. I am not sure if you could just replace 'now' with your numeric field or if you will need to massage the numeric field into a string.

Alternatively you could cast the number as a string and then do some substr() functions to pull the year month and day out into the correct format.

As to handling the 99999999 you may need to wrap this in a case statement to catch and deal with it seperatly.

I hope this starts to help you out.

Thanks,
Michael

[adbdkb]

Thanks.  So, when using the date(...) function, the first parameter is the string.  I  am trying to understand why it shows many modifiers and what is expected for the modifiers ?

date(timestring, modifier, modifier, ...)
datetime(timestring, modifier, modifier, ...)
strftime(format, timestring, modifier, modifier, ...)
julianday(timestring, modifier, modifier, ...)

I am a bit confused about what is needed for the modifiers.  Or how the modifiers are to be used

Also, where can I find an example of case statement that you have mentioned.

Thanks

[Michael_Litz]

Here is a substring that will rearrange the format:

substr($NumericField,5,2) || '/' || substr($NumericField,7,2) || '/' || substr($NumericField,0,5)

[Michael_Litz]

Here is a quick example of a case statement format:

CASE
WHEN $AR_H_STREAM_POSITION != ''
THEN
strftime('%Y-%m-%d',$HCD_FROM_YMD) = source_lookup('NO_CACHING', 'DBO', 'MyMaxTest', 'max(HCD_FROM_YMD)','1=?', '1')
ELSE 1
END

As for the documentation on the date time functions the best bet is to google SQLite syntax and check out there links.

Thanks,
Michael

[adbdkb]

Thanks.  I am not sure what am I missing.  When I do 'Test Expression',  nothing happens.  I will take a look at SQLite for syntax.  Just want to make sure the way I am calling it here is correct in terms of using it inside Replicate 

[Michael_Litz]

Hi adbdkb,

Try just using the substring it may be all you need - remove the string time function as the substring will put things into a date format.

Thanks,
Michael

PREVIE

PREVIE

[adbdkb]

Thanks.  Will try that.  Can the CASE statement still be added to check if the value is all 9, then set the value to say 20991231 ?

[Michael_Litz]

Hi adbdkb,

Yes, that is what I would do.

Thanks,
Michael

[Heinvandenheuvel · Accepted solution]

Just declare the column as DATE (whichever flavor) using a transformation with SUBSTR functions on the NUMERIC to take it apart and glue back together with dashes.

SQLite will do the number to text for you, as well as the text to date.

The below works for me (those \n are non-functional whitespace newlines to enhance readability in the UI:

The tricky part is to decide what to do with invalid dates. For the you could first feed the thusly  assembled  date string into another date-time function and look for a result. If there is a valid result provide the assembled (again) date else a trigger value like 1900-01-01. That's left as an exercise to the reader based on actual needs. I would NOT bother with 1900 < YYYYY < 2300, 0 < MM < 13, 0 < DD < 32.

Hein.

"table_manipulation":	{
	"owner":	"dbo",
	"name":	"test",
	"transform_columns":	[{
			"column_name":	"yyyymmdd",
			"action":	"KEEP",
			"new_data_type":	"kAR_DATA_TYPE_DATE",
			"computation_expression":	"substr($yyyymmdd,1,4) || '-' ||\nsubstr($yyyymmdd,5,2) || '-' ||\nsubstr($yyyymmdd,7,2)\n"
		}, ....