Build URL from three columns
Answered
I have a CSV with three columns w/headers: IMSI, FromTime, ToTime
111111111111111, 2022-03-07 21:00:00, 2022-03-07 23:00:00
IMSI is a typically 15 digits
FromTime and ToTime are Timestamps
I would like to build a URL and pass these three fields into it. The URL I'm trying to build is:
https://nm-demo-portal1.pplab.jdsu.net/viewport-ui/nav/diagnostics/?fromtime=2022-03-07 21:00:00&totime=2022-03-07 23:00:00&imsi=111111111111111
What is the best way to concatenate these together to form the URL?
The same question
Hi Simon, thanks for the suggestion. It sorta worked but the calculated concat field complained about not being able to find columns fromtime, totime, imsi. I had to add the actual CSV column name for it to work. Is this normal or should I be able to use the column name as it appears in the report builder/view?
CONCAT('https://nm-demo-portal1.pplab.jdsu.net/viewport-ui/nav/diagnostics/?fromtime=',f383014fromtimedate,'&totime=',f383016totimedate,'&imsi=',f383007imsi)
Note that when I prepared the data I converted the fromtime and totime to Timestamps which are fromtimedate and totimedate, respectively.
Attached is my test IMSI csv file if you want to try it.
Edit: Tried it again without converting the fromtime/totime to Timestamps and attached the error message.
Hi Simon, thanks for the suggestion. It sorta worked but the calculated concat field complained about not being able to find columns fromtime, totime, imsi. I had to add the actual CSV column name for it to work. Is this normal or should I be able to use the column name as it appears in the report builder/view?
CONCAT('https://nm-demo-portal1.pplab.jdsu.net/viewport-ui/nav/diagnostics/?fromtime=',f383014fromtimedate,'&totime=',f383016totimedate,'&imsi=',f383007imsi)
Note that when I prepared the data I converted the fromtime and totime to Timestamps which are fromtimedate and totimedate, respectively.
Attached is my test IMSI csv file if you want to try it.
Edit: Tried it again without converting the fromtime/totime to Timestamps and attached the error message.
Hi Simon,
I think I have this mostly figured out. Just one final question and I'm not sure if you can answer it because it may have to do with the backend application or input CSV, but thought I'd ask anyway.
When I click on the URL it successfully launches a new window and takes me to the URL. The fromtime, totime, and imsi parameters are passed to the backend application. I noticed, however, if the fromtime and totime dates are 2022-03-07 13:00 and 2022-03-07 14:00, respectively, the backend application interprets these dates as GMT timezone and converts them into Central timezone (-06:00). So 13:00 becomes 7:00 and 14:00 is 8:00. Is there any way to pass the timezone in the URL or perhaps include the timezone in the date function?
Here's my URL for two IMSI examples:
Hi Simon,
I think I have this mostly figured out. Just one final question and I'm not sure if you can answer it because it may have to do with the backend application or input CSV, but thought I'd ask anyway.
When I click on the URL it successfully launches a new window and takes me to the URL. The fromtime, totime, and imsi parameters are passed to the backend application. I noticed, however, if the fromtime and totime dates are 2022-03-07 13:00 and 2022-03-07 14:00, respectively, the backend application interprets these dates as GMT timezone and converts them into Central timezone (-06:00). So 13:00 becomes 7:00 and 14:00 is 8:00. Is there any way to pass the timezone in the URL or perhaps include the timezone in the date function?
Here's my URL for two IMSI examples:
Replies have been locked on this page!