MSBI - Business Intelligence

  # Load the necessary packages: httr and jsonlite install.packages ( "httr" ) install.packages ( "jsonlite" ) library ( httr ) library ( jsonlite ) library ( dplyr ) # Set up the API endpoint URL and the API key as variables. url <- "https://www.reed.co.uk/api/1.0/search?keywords=analyst&locationName=London&distancefromlocation=55&employerId=575264&resultsToTake=100" api_key <- authenticate ( user = "ADD API KEY HERE" , password = "" , type = "basic" ) # Create an empty list to store the API responses. responses <- list ( ) # Set up a for loop that iterates over a sequence of values that you want to query the API for. seq ( from = 1 , to = 1001 , by = 100 ) # Create a sequence going up by 100 for ( i in seq ( from = 1 , to = 1001 , by = 100 ) ) { # Construct the API request URL with the i value and the API key ...

  # Install packages httr and jsonlite install.packages("httr") install.packages("jsonlite") # Create a GET response to call the API ``` {r} jobsearch <- httr::GET("https://www.reed.co.uk/api/1.0/search?keywords=analyst&location=london&distancefromlocation=15?details=true", httr::authenticate( user = " 686dc8ba-b318-4d65-ae64-7387613517230000000000 ", password = "", type = "basic")) # View API GET response result str(jobsearch) # View the main source of the data needed from the API - Normally content str(jobsearch$content) ``` # Convert the raw content to text (in this case, a JSON string) ``` {r} jobsearchcontent <- httr::content(jobsearch, as = "text") # View JSON String result str(jobsearchcontent) ``` # Convert the JSON string to a dataframe and view data in a table ``` {r} jobsearchJSON <...

  # Install packages httr and jsonlite ``` {r} install.packages("httr") install.packages("jsonlite") ``` # Create a GET response to call the API ``` {r} foodbank <- httr::GET("https://www.givefood.org.uk/api/2/foodbanks/") # View API GET response result str(foodbank) # View the main source of the data needed from the API - Normally content str(foodbank$content) ``` # Convert the raw content to text (in this case, a JSON string) ``` {r} foodbankcontent <- httr::content(foodbank, as = "text") # View JSON String result str(foodbankcontent) ``` # Convert the JSON string to a dataframe and view data in a table ``` {r} foodbankJSON <- jsonlite::fromJSON(foodbankcontent) View(foodbankJSON) ```

.. https://ubiquis.co.uk/loading-csv-files-with-pdi-metadata-injection-2/ -- Create a temporary table to demonstrate the solution CREATE TABLE YourTable ( ID INT, YourColumn VARCHAR(100) ); -- Insert sample data INSERT INTO YourTable (ID, YourColumn) VALUES (1, '1234_4567_abc'), (2, '789_012_def'), (3, 'xyz'); -- Declare variables for storing results DECLARE @firstPart VARCHAR(100), @secondPart VARCHAR(100); -- Iterate over rows in the table DECLARE @currentID INT = 1; DECLARE @rowCount INT = (SELECT COUNT(*) FROM YourTable); WHILE @currentID <= @rowCount BEGIN -- Retrieve input string from the table DECLARE @inputString VARCHAR(100); SELECT @inputString = YourColumn FROM YourTable WHERE ID = @currentID; -- Check if the delimiter exists in the input string IF CHARINDEX('_', @inputString) > 0 BEGIN -- Find the position of the first occurrence of the delimiter in the input string DECLARE @delimiter...