Qlik/SQL Server技术求助：获取特定标识对应位置的关联值

Hi there! Let's break down how to solve this in both SQL Server and Qlik environments, using your sample data as a reference.

SQL Server Approach #

If you're working directly in SQL Server (2022+ or Azure SQL Database), the cleanest way is to use STRING_SPLIT with the optional ordinal parameter, which preserves the position of each element in the delimited string. Here's a query that does exactly what you need:

-- Replace 'YourTableName' with your actual table name
WITH A_Positions AS (
    SELECT 
        *,
        s.ordinal AS A_Index
    FROM YourTableName
    CROSS APPLY STRING_SPLIT([Column A], ';', 1) s
    WHERE s.value = 'A'
)
SELECT 
    [Column A],
    [Column B],
    s.value AS Matching_B_Value
FROM A_Positions
CROSS APPLY STRING_SPLIT([Column B], ';', 1) s
WHERE s.ordinal = A_Index;

How this works: #

1. The CTE A_Positions splits each Column A string into individual elements, finds the position (ordinal) where the value is 'A', and keeps that index alongside the original row data.
2. We then split Column B using the same delimiter, and pick the element at the same ordinal as the 'A' we found in Column A.

Running this on your sample data will return:

Note for older SQL Server versions:

If you're using SQL Server pre-2022 (where STRING_SPLIT doesn't support ordinal), you'll need a recursive CTE or a custom string-splitting function that preserves order. Let me know if you need that version!

Qlik Sense/QlikView Approach #

In Qlik, this is straightforward using built-in string functions. You can create a calculated dimension or measure with the following expression:

SubField([Column B], ';', Index([Column A], 'A', ';'))

How this works: #

• Index([Column A], 'A', ';') finds the position of 'A' when Column A is split by semicolons (e.g., in your second row, this returns 2 because 'A' is the second element).
• SubField([Column B], ';', position) extracts the element from Column B at that exact position.

Adding this as a calculated field in your Qlik app will automatically return the correct corresponding value from Column B for each row where 'A' exists in Column A.

Hope this solves your problem! Let me know if you need further clarification on either approach.

内容的提问来源于stack exchange，提问作者Jase