Substring

[chandraprakash_j_volvo]

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?

[john_wang · Accepted solution]

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.

[Heinvandenheuvel · Accepted solution]

@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.

[john_wang]

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.