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

[adbdkb]

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

[adbdkb]

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

[Heinvandenheuvel]

---

@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

[adbdkb]

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?

[adbdkb]

Output from the transformation function

[Heinvandenheuvel]

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

[adbdkb]

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

[Heinvandenheuvel]

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

[adbdkb]

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?

[Heinvandenheuvel]

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.