Skip to main content
Announcements
Live today at 11 AM ET. Get your questions about Qlik Connect answered, or just listen in. SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
chandraprakash_j_volvo
Contributor III
Contributor III

Substring

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?

2 Solutions

Accepted Solutions
john_wang
Support
Support

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)

john_wang_0-1706004042417.png

The output looks like:

john_wang_1-1706004092452.png

Hope this helps.

John.

Help users find answers! Do not forget to mark a solution that worked for you! If already marked, give it a thumbs up!

View solution in original post

Heinvandenheuvel
Specialist II
Specialist II

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

View solution in original post

3 Replies
john_wang
Support
Support

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)

john_wang_0-1706004042417.png

The output looks like:

john_wang_1-1706004092452.png

Hope this helps.

John.

Help users find answers! Do not forget to mark a solution that worked for you! If already marked, give it a thumbs up!
Heinvandenheuvel
Specialist II
Specialist II

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

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)

john_wang_0-1706101855043.png

Best Regards,

John.

Help users find answers! Do not forget to mark a solution that worked for you! If already marked, give it a thumbs up!