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

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

adbdkb — Thu, 26 May 2022 19:28:13 GMT

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

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

Michael_Litz — Thu, 26 May 2022 19:59:55 GMT

Hi adbdkb,

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

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

adbdkb — Thu, 26 May 2022 20:08:07 GMT

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

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

Michael_Litz — Thu, 26 May 2022 20:12:10 GMT

Here is a substring that will rearrange the format:

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

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

Michael_Litz — Thu, 26 May 2022 20:15:36 GMT

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

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

adbdkb — Thu, 26 May 2022 20:26:16 GMT

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

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

Michael_Litz — Thu, 26 May 2022 20:29:34 GMT

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

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

adbdkb — Thu, 26 May 2022 20:35:00 GMT

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 ?

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

Michael_Litz — Thu, 26 May 2022 20:41:21 GMT

Hi adbdkb,

Yes, that is what I would do.

Thanks,
Michael

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

Heinvandenheuvel — Thu, 26 May 2022 20:52:13 GMT

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" }, ....

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

adbdkb — Fri, 27 May 2022 13:56:08 GMT

And I will have to do this for each DATE column, right?

OR

Will I be able to define a common transformation routine and apply it to any date ?

If the common routine is possible, how would I achieve that?

Thanks

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

adbdkb — Sat, 28 May 2022 15:30:47 GMT

So, I defined this for transformation and set the column type as DATE

CASE
WHEN $POLICY_ISSUE_DATE == 99999999
THEN
strftime('%m-%d-%Y','2199-12-31')
ELSE
strftime('%m-%d-%Y', substr($POLICY_ISSUE_DATE,0,5) || '-' ||
substr($POLICY_ISSUE_DATE,5,2) || '-' ||
substr($POLICY_ISSUE_DATE,7,2))
END

When I test the expression in the builder, it works fine for both 99999999 as well as e.g. 20220523

But when I run the task that has this transformation, I get the below error

(subtask 1 thread 1) is suspended. Failed to init column calculation expression

How can I debug this in Qlik? I do not have direct access to see the data in the database, hence I cannot easily check the incoming value from database

Thanks

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

Heinvandenheuvel — Sun, 29 May 2022 02:25:08 GMT

@adbdkb wrote:

So, I defined this for transformation and set the column type as DATE

:

ELSE
strftime('%m-%d-%Y', substr($POLICY_ISSUE_DATE,0,5) || '-'

Hmm, I cannot try just now but I do not see the value of using strftime for this use case as all it will deliver is a string in the exact same format it was provided with. More moving parts, more possible issues, more overhead.

Case in point, the format used is incorrect. It should be %Y-%m-%d.

Furthermore, the substr function offset argument is 1 based and using 0 may fail or produce surprise results. Plus don't you want 4 chars for the year, not 5 as requested.

Hein

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

adbdkb — Sun, 29 May 2022 02:34:43 GMT

When I used it without strftime - the data always showed "0000-00-00" in Kafka. That is why wanted to use the strftime since I had set the column type as DATE. But I tried setting it string and used without strftime, but still was getting error. Both the formats, when tested in the UI, give the expected output?

I also observed I had used below to test creating a column using function. That also gives the exact same error

(subtask 1 thread 1) is suspended. Failed to init column calculation expression

'{
"key2": {
"COMPANY_CODE": "' || $$COMPANY_CODE || '",
"MASTER_ID": "' || $$MASTER_ID || '"
}
}
'

How should we define functions in the UI for transformation?

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

adbdkb — Sun, 29 May 2022 02:46:51 GMT

Output from the transformation function

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

Heinvandenheuvel — Sun, 29 May 2022 13:53:53 GMT

You are not listening/reading or were misguided by an early incorrect example.

Dates are YYYY-MM-DD not the crappy (American) MM-DD-YYYY

My example was fully tested with Oracle Source, SQLserver date2 column output.

The string example looks all broken also.

What is the intend with the $$COMPANY_CODE? Variables are introduced with a single dollar sign. If you want a dollarsign in the result it should be within the single quotes: ..."$' || $COMPANY_CODE || '",....

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

adbdkb — Sun, 29 May 2022 15:27:19 GMT

I wasn't / am not sure how to define this in the UI.

"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"
		}

I shall try just the expression starting with substrate and test again. Is the CASE statement syntax part correct? To handle 999999999 ?

The $$COMPANY_CODE is a typo / mistake on my part. Didn't realise it had duplicate $ sign.

Thanks

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

Heinvandenheuvel — Sun, 29 May 2022 20:14:07 GMT

That's the result of an export task.

Once you understand/know what you want change the JSON is often easier IMHO, notably for bulk changes. Just feed a smart script a list of table/column names and let it spit out the desired ammended json. Import.

Export your own and you'll see how a new-line in the gui maps to \n - which I explained to be just whitespace. To use in the GUI retype along the pattern or go into a text editor, past the expression, replace my example variable names with the real ones and remove \n or replace with line-breaks as desired.

The case statement looks good, except that the strftime complexity breaks the correct and sufficient '2199-12-31' - get rid of it. KISS

Hein

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

adbdkb — Sun, 29 May 2022 20:28:14 GMT

Thanks @Heinvandenheuvel - now I understood how that json part should be used. I just started using Qlik a couple of weeks ago and still trying to figure out how I can best use the functionality it provides. I was asked to create Qlik task using the DB2 input and Kafka json output. And I have been trying to look at examples and documentation to get it working.

I really appreciate the help / guidance that you have provided

I will apply the above method and update all the date fields after first exporting the task as a json file.

One more ( hopefully last ) question

I basically have to create a key column in JSON format by using the 2 primary keys from the DB2

I tried this way, but it puts \" in the output. I just want to have the expression as json

'{' ||
'"CompanyCode":"' || $COMPANY_CODE || '",' ||
'"MasterId":"' || trim($MASTER_ID) || '"' ||
'}'

What do I need to do to get the correct value in json format?

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

Heinvandenheuvel — Mon, 30 May 2022 22:10:47 GMT

I only tested the expression in the GUI. Parse prompts for COMPANY_CODE and MASTER_ID as expected.

Providing 'aap' and 'noot' as values the Test expression results are: {"CompanyCode":"aap","MasterId":"noot"}

That's what I expected. Hmmm.

Hein.