Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
rupaliqlik
Creator
Creator

Which qlik function should use to convert SWITCHOFFSET in qliksense

Hi Folks,

I want to convert sql script into qliksense.

but below function is date function in scql.Which date frunction I shoul duse to convert it into qliksense.

SQL: SWITCHOFFSET(g.CreatedDate, '+05:30')

Please help me to understand this.

Thanks,

Rupali M

#Scripting

#SQL To Qlik

Labels (3)
3 Replies
anthonyj
Creator III
Creator III

Hi @rupaliqlik,

Assuming your CreatedDate is in a datetime format you can use this to add 5:30 to the field.

timestamp(TIMESTAMP#(CreatedDate, 'DD/MM/YYYY hh:mm:ss TT') + time('5:30')) as NewCreatedDate,

If CreatedDate is only a date without the time already populated you can add:

timestamp(CreatedDate + time('5:30')) as NewCreatedDate,

Depending on your use case you may just need to use the converttolocaltime() function.

Regards

Anthony

rupaliqlik
Creator
Creator
Author

HI,

@Anthonyj I am getting below result When I am using  timestamp(CreatedDate + time('5:30')) as NewCreatedDate.

Expected result is   2022-03-02 20:00:01.777 +05:30

 

rupaliqlik_0-1649741626798.png

BTW timestamp(TIMESTAMP#(CreatedDate, 'DD/MM/YYYY hh:mm:ss TT') + time('5:30')) as this is not working 

Thanks,

Rupali Ethape

@abhijitnalekar

anthonyj
Creator III
Creator III

Hi Rupali,

Your timestamp is in a different format than my assumption but it looks like your set variables are taking care of this for you.

timestamp(TIMESTAMP#(date, 'YYYY-MM-DD hh:mm:ss.ffffff') + time('5:30'))

So, it looks like the function is performing the task of adding 5:30 to the output but you're looking for an output that shows the original created time but include the text of +5:30 on the end.

If you're looking to add the text to the end of the create date then you can add the field CreatedDate & ' +5:30' to your dimension. This will give the result of 2022-03-02 20:00:01.777 +05:30 in your table. You can also add it in the load script which will create a text type column.

Apologies if I've misunderstood your requirement.

Thanks

Anthony