Which qlik function should use to convert SWITCHOFFSET in qliksense

[rupaliqlik]

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

[anthonyj]

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]

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

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

Thanks,

Rupali Ethape

@abhijitnalekar

[anthonyj]

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