topic Re: Substring in Qlik Replicate

Substring

chandraprakash_j_volvo — Tue, 23 Jan 2024 09:31:30 GMT

I have added CDCT column through global rules by adding "$AR_H_COMMIT_TIMESTAMP" command. And I have to use the CDCT column to fetch only year and month.

Example:

CDCT : 2024-01-17T12:55:42.809+0000

Output I expect : 202401

What functions I need to use in Global Rules and how?

Re: Substring

john_wang — Tue, 23 Jan 2024 10:01:53 GMT

Hello @chandraprakash_j_volvo ,

Thanks for reaching out to Qlik Community!

There are different expressions can get such value, one of the easy-understanding expression is using substr function, the expression like:

substr(date($AR_H_COMMIT_TIMESTAMP),1,4)||substr(date($AR_H_COMMIT_TIMESTAMP),6,2)

The output looks like:

Hope this helps.

John.

Re: Substring

Heinvandenheuvel — Tue, 23 Jan 2024 18:52:50 GMT

@john_wang offers a fine solution

The 'official' solution, perhaps faster and more readable is to use STRFTIME ( https://www.sqlite.org/lang_datefunc.html )

Just use this as a edit/parse/test to get going: strftime('%Y%m', 'now')

btw... I can't help to make a pedantic / amuzed observation. I mean no harm, but check out the subject: "substring" 1) how it that going to help future reader with a similar challenge to find a solution. 2) a solution is being implied/suggested. A subject line like "transformation to get year and month (substring) from date desired.". Grins, Hein.

Hein.

Re: Substring

john_wang — Wed, 24 Jan 2024 13:12:05 GMT

Hello @Heinvandenheuvel ,

Glad to get your input here! Yes I'm sure the latter expression performance is better than mine.

Hello @chandraprakash_j_volvo ,

As explained there are many different expression, the better one from Hein is:

strftime('%Y%m', $AR_H_COMMIT_TIMESTAMP)

Best Regards,

John.