BAQ ~List~ handling

[jbooker (Josh Booker)]

@William

This is PARSENAME()

Thanks. Good idea! I’ve used PARSENAME() before as well. Had not thought of substituting separator. Thanks for sharing.

Also, you can use STRING_SPLIT to return separated values as rows like so:

SELECT value, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS ordinal
FROM STRING_SPLIT('apple~banana~orange', '~');

value	ordinal
apple	1
banana	2
orange	3

[jbooker (Josh Booker)]

InvcHead SalesRepList example:

Trying to create a BAQ that lists sales by salesperson. Problem is SalesRepList can hold multiple. Help!

With MSSQL 2016+ you can cross apply string_split like so (remove ordinal for better perf or not 2022+) SELECT InvcHead.Company AS InvcHead_Company, InvcHead.InvoiceNum AS InvcHead_InvoiceNum, s.value AS Split_SalesRep, s.ordinal AS SalesRep_Order -- mssql2022+ FROM InvcHead CROSS APPLY STRING_SPLIT(InvcHead.SalesRepList, '~', 1) s ORDER BY InvcHead.InvoiceNum, s.ordinal; InvcHead_Company InvcHead_InvoiceNum Split_SalesRep SalesRep_Order 111111 1001 RepA 1 111111 10…

[klincecum (Kevin Lincecum)]

@Banderson

That’s string_agg().

What am I missing here?

I think I was confusing it with this:

Function: convertDelimitedListToSQL?

convertDelimitedListToSQL is really just pure UI function, so this is question to UX team, not for Patrick and I. Description is: This function gets a SQL compatible list for use in SQL where clauses. Example item one~item two~item three~ -----> ‘item one’,‘item two’,‘item three’ OR 1~2~3~ -----> 1, 2, 3 As for tilda separated list in BAQ this is what I thought about, when I said I saw it here: So you can use function like [Ice].num_entries(QuoteDtl.ConfigData_c, ‘~’ ) > 0 but be aware…

[CSmith (Clint Smith)]

Parsename ONLY reliably works if you do not have any preexisting periods in your dataset(s).