Sub String lookup for Multiple Values contained in a cell.

KeithSP — Thu, 22 Jun 2023 22:13:43 GMT

I have two queries. One query contains a list of users with one value (Approved by) per row. I have another query that lists more than one value in the cell (Approvers). The values are separated by a delimiter. I want to look up to see if the Approved By is listed in the Approver report, with a simple yes no result. For example, if this was excel it would be IF(Approver By = "*"Approver"*", "Yes","No")

TIA

Re: Sub String lookup for Multiple Values contained in a cell.

rbriga — Fri, 23 Jun 2023 03:38:52 GMT

Let's solve this in the query.

Take the second query, use a function to seperate the "approved by" list into rows.

Then inner join this table with the users table.

In SQL, this can be:

Now, inner join this table with the users table.

WITH _List AS ( SELECT [ID], value AS [Approved By] FROM [YourTable] CROSS APPLY STRING_SPLIT([Approved By], ',') ), _Valid_Approvers AS ( SELECT _List.ID, COUNT(*) AS Approvers_Count FROM _List INNER JOIN [ApproversTable] AT ON AT.Approver = _List.Approver GROUP BY _List.ID ) SELECT [YourTable].*, CASE WHEN VA.Approvers_Count IS NULL THEN 'No', ELSE 'YES' END AS Has_Valid_Approvers FROM [YourTable] C LEFT JOIN _Valid_Approvers VA ON C.[ID] = VA.[ID]

topic Re: Sub String lookup for Multiple Values contained in a cell. in DAX Commands and Tips

Sub String lookup for Multiple Values contained in a cell.

Re: Sub String lookup for Multiple Values contained in a cell.