Challenge 26: Visitor Visa Applications
Level: Medium
Description: Diplomacy and international relations are inherently complex, with each country maintaining its own unique network of relationships with others. For non-Europeans planning a trip to Europe, for instance, obtaining a Visitor Visa may be required—and acceptance rates can vary significantly depending on the applicant’s nationality. This week, we’ll use KNIME’s Network Mining extension to explore which pairs of countries show higher acceptance or refusal rates for Visitor Visa applications across Europe.
Beginner-friendly objectives: (1) Read the dataset on Visitor Visa applications to different European countries. (2) Preprocess the data and derive acceptance and refusal rates for each pair of countries (where one of them is an European country).
Intermediate-friendly objective: (1) Create a visualization that, given a selected European country, shows which other countries have the highest acceptance and the highest refusal visa application rates.
Solution Summary: The solution reads the 2023 Schengen visa consular statistics, cleans and reduces the dataset to the necessary fields, and aggregates by Schengen State and consulate location to derive refusal and acceptance rates. It introduces an interactive dropdown to select a focus state, filters the data accordingly, and identifies the top 25 consulates by acceptance and by refusal. Finally, it renders two network graphs where edge weights encode acceptance or refusal intensity between consulate locations and the selected state, all wrapped in a component view for dashboard-style interactivity.
Solution Details: - Data ingestion and cleaning: An Excel Reader loads the “2023-schengen-visa-statistics-consulates_en.xlsx.xlsx” file using the first row as headers and replacing empty strings with missing values. A Missing Value node removes rows with any missing String fields, ensuring keys like “Schengen State” and “Country where consulate is located” are present. A Column Filter then excludes a set of visa-detail columns, keeping the key fields such as Schengen State, Country where consulate is located, and Not issued rate for ATVs and uniform visas. - Aggregation and metrics: A GroupBy groups by Schengen State and Country where consulate is located and takes the first non-missing Not issued rate for ATVs and uniform visas per pair. A Math Formula computes acceptance rate as 1 − refusal, and a Column Renamer standardizes the refusal-rate column name to visitor_visa_refusal_rate. In parallel, a Sorter and Duplicate Row Filter produce a unique, ordered list of Schengen States to populate the selector control. - Interactivity and filtering: A Value Selection Widget provides a dropdown labeled “Select focus country (EU/Schengen)” driven by the Schengen State column and exposes the chosen value as a flow variable (from-country). A Row Filter keeps only records matching the selected Schengen State. Note: ensure this filter uses the widget’s flow variable rather than a hard-coded value so that the view updates dynamically. - Ranking: Two Top k Row Filters identify the top 25 pairs by visitor_visa_acceptance_rate and by visitor_visa_refusal_rate, respectively, pushing missing values to the end so only valid scores are considered. - Network construction and visualization: For each ranked subset, an Object Inserter constructs a directed network with “Country where consulate is located” as source nodes and the “Schengen State” as target nodes. Edge weights and IDs are taken from the corresponding metric (acceptance or refusal). Two Network Viewers render these networks: one titled “Countries with Highest Acceptance Rate” (edges weighted and labeled by acceptance) and another titled “Countries with Highest Refusal Rate” (weighted and labeled by refusal). Each view uses a COSE layout by default, exposes layout and selection controls, and formats edge labels to two decimals. - Component view: Inside the component, a Text Output Widget displays the title “Visitor Visa - Acceptance and Refusal Rates,” and the two network views are presented side by side as the interactive dashboard. The component’s input is the prepared table; early completion is enabled for responsiveness.