Skip to main content

Hello everyone,

 

Unfortunately I have a field that says something like: “A|B” or also “A|B|C” or "B|C". I don't know in advance how many different combinations there are, but I have predefined categories. Now I want to create a diagram in which I want to display the frequency of the individual elements. If I do something like:

 

Case

when Table1.Column Like '%A%' then 'A'

when Table1.Column Like '%B%' then 'B' etc.

end

 

Then it aborts after the first occurrence and does not count the other elements. But I would like to have them all counted in each associated “pot”.

 

Several case when statements in a row do not work, even union does not work in PQL as far as I know. With STRING_SPLIT I can cut after the “|”, but I don't know how I can then aggregate the individual occurrences again.

 

I would also be interested to know how I can count the number of occurrences of “|” in a string

 

Does anyone have an idea? Thank you!

 

 

Hi Tristan, feels in general like a problem you would rather solve in Backend using Vertica SQL than relying on PQL.

 

Unfortunately I am not aware of a PQL formala being capable of that in a straight forward way.

However a super messy approach I could think if of is a bunch of nested case whens. Utilizing PATINDEX() to find index of a '|' character and then processing that A and then RIGHT(index you where calculating) to removing one block after another and then processing the next one. But this would only be viable if you have a somewhat low number of concatinations. And you would have to cater for different lengths (e.g. one case if only one | is there, one case when if there are two and so forth.)

 

I would definetely try to solve in SQL as this is super messy and hard to maintain.

 

For the counting: One idea might be to just use a simple LEN() if you A,B,C values are actually of same lenght you can deduct the number of | by the number of total characters. But this is would need the above assumption.

Hi Manuel, thanks for your quick reply. I half expected that i have to do it in the backend..

 

Thanks also for your approach, but I don't know how many different combinations there are, so it's difficult..

 

I have already solved the counting part in this way: LEN("Table1"."Column1") - LEN(REPLACE("Table1"."Column1", '|', '')) + 1.

Since the number of parts is always one greater than the number of separators, the plus 1

 

 

 

Hi @t.m.,

 

Just add 'MULTI ' before your CASE WHEN statement in PQL, and it should continue to process once a match is made.

 

Let me know if this helped.

 

Best regards,

Jan-peter

Hi @t.m.,

 

Just add 'MULTI ' before your CASE WHEN statement in PQL, and it should continue to process once a match is made.

 

Let me know if this helped.

 

Best regards,

Jan-peter

Hi. Thank you! That works and i didn't knew this statement. Thanks a lot. I builded it already outside with SQL but thats a smart solution to keep in mind for the next time!

Reply


Terms & ConditionsCookie settings