Build URL from three columns

Ryan Kearbey shared this question 2 months ago
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?

Comments (5)

photo
1

Hi Ryan,


The easiest way would be to create a Calculated Field, using the CONCAT() function.


For example, CONCAT('https://nm-demo-portal1.pplab.jdsu.net/viewport-ui/nav/diagnostics/?fromtime', fromtime, '&totime=', totime, '&imsi=', imsi). You could then format it as a URL Hyperlink.


/99ac81009b0a9b2c9ffacb5535bea474


Let me know if this answers your question.


Kind regards,

Simon

photo
1

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.

photo
1

Hi Ryan,


Yes that is correct, you must use the column reference as defined in the database schema when creating calculated fields. That format you have provided is correct for CSV Data Sources.


I'm not sure I understand what you mean by the conversion, however, note that the data you provided in the CSV is in a different format to what you are asking for in the original post. You will need to run a Date Function to create a new column for the timestamp values. Note that even though you've formatted the 'totime' and 'fromtime' columns to display the date in a particular way, the underlying data has not changed.


Kind regards,

Simon

photo
1

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:

/da41724a57e85fbcba7882aeb1e2751e

photo
1

Hi Ryan,


This article should be what you are looking for.


You need to 'convert' the timestamp by adding a timezone offset to the column. This lets Yellowfin know that the relative timezone for that column is, with respect to the Yellowfin server/ database.


/938a567a6bf0995a9665e186d89157b7


Let me know if you have any further questions.


Kind regards,

Simon

photo
1

Thank you Simon let me try this. I guess you can close this ticket.

photo