Extract Multiple Occurrences of Text Between Characters

[KD2015]

Hello Experts,

I have a field with some IDs which look like this...

a:5{i:0;s:2:"46";i:1;s:2:"47";i:2;s:2:"68";i:3;s:...

I want to extract only the integers between " " and have them separated by a comma e.g. 46,47,68. I have seen many solutions posted as answers to somewhat similar scenario inquiries, but I have not been able to locate a solution for something similar to my issue here. Please help.

Thank you.

[mikaelsc · Accepted solution]

something combining textbetween and substringcount?

https://help.qlik.com/en-US/qlikview/May2024/Subsystems/Client/Content/QV_QlikView/Scripting/StringF...

inline: 
load * inline [
inline
a:5{i:0;s:2:"46";i:1;s:2:"47";i:2;s:2:"68";i:3;s:...
];

temp: 
load 
	inline,
    substringcount(inline,'s:2:')		as substringcount
resident inline; 

temp2: 
load 
	inline, 
    TextBetween(inline,'s:2:',';',1)		as txtb1,
        TextBetween(inline,'s:2:',';',2)		as txtb2,
                TextBetween(inline,'s:2:',';',3)		as txtb3
resident temp;

this is just to show the expressions... should be made more robust by iterating over the records from 1 to substringcount, to generate 1 row per unique id,... 

[Kushal_Chawda · Accepted solution]

@KD2015  Tyr below

I am assuming that identifying unique numbers starts with ':"' if it is something different like 's:2:' You can change it as highlighted to take correct substringcount. This approach has performance benefit  as it doesn't iterate over actual data without performing any aggregation.

Data:
Load ID_Field,
         SubStringCount(ID_Field,':"') as Count_characters
FROM Table;

// ************ Build TextBetween Formula******************

max_characters:
Load max(FieldValue('Count_characters',RecNo())) as max_characters
AutoGenerate FieldValueCount('Count_characters');

let vMaxCharactes =Peek('max_characters');

Drop Table max_characters;

formula_temp:
Load IterNo() as Rank,
       'TextBetween(ID_Field,'':"'',''"'',' & IterNo() & ')' as formula
AutoGenerate 1
while IterNo() <= '$(vMaxCharactes)';

// highlighted below is not double quote they are single quotes

formula:
Load Concat(formula,'& '' '' &', Rank) as formula 
Resident formula_temp;

let vTextBetweenFormula = Peek('formula');

Drop Table formula_temp;

// **********************************************************

Final:
Load ID_Field,
         replace(RTrim($(vTextBetweenFormula)),' ',',') as RequiredField
Resident Data;

Drop Table Data;

[KD2015]

Thank you, this gave me a starting point. I have built some logic with it, but as you said, I need to figure out how to iterate since some rows have more s:2 values than others. But yes, thanks a lot 

[KD2015]

This worked like Charm. Thank you