Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have a date&time field in my "Activity table" (Eloqua database)
Date |
3/24/2017 1:05 |
2/23/2017 11:30 |
and I have converted it to TimeBucket field:
Dual(Time([Date],'h tt')&' - '&Time([Date]+'01:00','h tt'), Hour([Date])) as TimeBucket,
I also have a Country and Region field in my "Contacts" Table:
Country | Region |
Spain | EMEA |
United States | NA |
South Africa | APAC |
Australia | APAC |
etc.. | etc… |
I have filters in my dashboard for regions -> APAC, EMEA, NA. I would like to know when the end user selects a APAC region filter he gets to see the data in the APAC local time?.
Use Convert to local time function
ConvertToLocalTime(timestamp [, place [, ignore_dst=false]])
Converts a UTC or GMT timestamp to local time as a dual value. The place can be any of a number of cities, places and time zones around the world.
Valid places and time zones:
Abu Dhabi, Adelaide, Alaska, Almaty, Amsterdam, Arizona, Astana, Athens, Atlantic Time (Canada), Auckland, Azores, Baghdad, Baku, Bangkok, Beijing, Belgrade, Berlin, Bern, Bogota, Brasilia, Bratislava, Brisbane, Brussels, Bucharest, Budapest, Buenos Aires, Cairo, Canberra, Cape Verde Is., Caracas, Casablanca, Central America, Central Time (US & Canada), Chennai, Chihuahua, Chongqing, Copenhagen, Darwin, Dhaka, Eastern Time (US & Canada), Edinburgh, Ekaterinburg, Fiji, Georgetown, Greenland, Greenwich Mean Time : Dublin, Guadalajara, Guam, Hanoi, Harare, Hawaii, Helsinki, Hobart, Hong Kong, Indiana (East), International Date Line West, Irkutsk, Islamabad, Istanbul, Jakarta, Jerusalem, Kabul, Kamchatka, Karachi, Kathmandu, Kolkata, Krasnoyarsk, Kuala Lumpur, Kuwait, Kyiv, La Paz, Lima, Lisbon, Ljubljana, London, Madrid, Magadan, Marshall Is., Mazatlan, Melbourne, Mexico City, Mid-Atlantic, Midway Island, Minsk, Monrovia, Monterrey, Moscow, Mountain Time (US & Canada), Mumbai, Muscat, Nairobi, New Caledonia, New Delhi, Newfoundland, Novosibirsk, Nuku'alofa, Nuku´alofa, Osaka, Pacific Time (US & Canada), Paris, Perth, Port Moresby, Prague, Pretoria,Quito, Rangoon, Riga, Riyadh, Rome, Samoa, Santiago, Sapporo, Sarajevo, Saskatchewan, Seoul, Singapore, Skopje, Sofia, Solomon Is., Sri Jayawardenepura, St. Petersburg, Stockholm, Sydney, Taipei, Tallinn, Tashkent, Tbilisi, Tehran, Tijuana, Tokyo, Ulaan Bataar, Urumqi, Warsaw, Wellington, West Central Africa, Vienna, Vilnius, Vladivostok, Volgograd, Yakutsk, Yerevan or Zagreb.
Also GMT, GMT-01:00, GMT+04:00 etc. are valid places.
The resulting time is adjusted for daylight savings time, unless the third parameter is set to 1 or true().
Examples:
ConvertToLocalTime(’2007-11-10 23:59:00’,’Paris’) returns ’2007-11-11 00:59:00’ and the corresponding internal timestamp representation.
ConvertToLocalTime(UTC(), ’GMT-05:00’) returns the time for the North American east coast, e.g. New York.
Hi Avinash,
Thank you for your post. Can you help me understand as to how should I be using this function in
Dual(Time([Date],'h tt')&' - '&Time([Date]+'01:00','h tt'), Hour([Date])) as TimeBucket
Using this function we could convert the time to their respective time zone
for e.g.
Region , Time
Paris,2007-11-10 23:59:00
New Delhi,2007-11-10 23:59:00
if you use
ConvertToLocalTime(Time,Region) as Local_time this will convert the time to their respective region time so for Paris it will set the Paris time and similar for Delhi
then you could
Dual(Time([Local_time ],'h tt')&' - '&Time([Local_time ]+'01:00','h tt'), Hour([Local_time ])) as TimeBucket
so based the region time will be shown
if you need all the time to be converted to one time zone specify only the time zone so all the time will be in one region format
Hope this helps you
Hi Avinash,
I did try the above two scripts.
It gives me the below error:
The following error occurred:
Field not found - <Country>
Do you the Country column ? to which the time needs to be converted ?
Local_Time is the field you have created inside table and you cannot refer same in immediate load
LOAD *,
Dual(Time([Local_time ],'h tt')&' - '&Time([Local_time ]+'01:00','h tt'), Hour([Local_time ])) as TimeBucket;
LOAD
other columns,
ConvertToLocalTime(Time,Region) as Local_time
...
Yes. I do have a Country column. And I have made sure that it is spelled correctly with C caps.
So you are saying the variable I created "Local_Time" cannot be used in - Dual(Time([Local_time ],'h tt')&' - '&Time([Local_time ]+'01:00','h tt'), Hour([Local_time ])) as TimeBucket;?
I have Date in my Activity table and Country in Contacts Table.
And the TimeBucket Field is in my Activity Table.
My Final output is something like this. So when the user selects a Country field he should get the data according to the local time of that selected country,
Hi Ajinkya,
I think this is not the right place to ask but I'm posting here as I see it is relevant to my problem or scenario.
By looking at your post, I assume that you have connected Qlik and Eloqua Database. If yes, will you help me in connecting? I tried through REST but I'm not getting proper parameters to pass.
I have posted different thread and also seen other threads from different users but no responses over there. So thought to ask you here.