Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

yura_ratu
Contributor II

Parsing fields from a string

Hi Community,

I need to reconstruct a table from a string. Input is as follows.

IDComment
1Field1 : [Old]ValueOld[New]ValueNew Field2 : [Old]Dummy Text Old [New]New Dummy Text New Field3 : [Old]Dummy Text Old [New]New Dummy Text New

Wanted ouput:

IDFieldNameOldValueNewValue
1Field1ValueOldValueNew
1Field2Dummy Text OldDummy Text New
1Field3Dummy Text OldDummy Text New

There could be any amount of Fields within a string, however their names are always without spaces


Stuck a little bit with transformations. Thanks for help!

Tags (1)
1 Solution

Accepted Solutions

Re: Parsing fields from a string

Hi,

one solution could be:

QlikCommunity_Thread_181465_Pic1.JPG

table1:

Generic

LOAD ID,

    SubField(SubComment,' : [',1) as FieldName,

    TextBetween(SubComment,'[',']',IterNo())&'Value' as ColNam,

    TextBetween(SubComment&'[',']','[',IterNo()) as ColVal

While IterNo() <= SubStringCount(SubComment,'[');

LOAD ID,

    Trim(Mid(Comment, Alt(Previous(Pos)+1,1),RangeSum(Pos,-Previous(Pos)))) as SubComment;

LOAD *,

    If(IterNo()=SubStringCount(Comment,' : ['),Len(Comment),Index(Left(Comment,Index(Comment,' : [',IterNo()+1)-1),' ',-1)) as Pos

INLINE "

    ID, Comment

    1, Field1 : [Old]ValueOld[New]ValueNew Field2 : [Old]Dummy Text Old [New]New Dummy Text New Field3 : [Old]Dummy Text Old [New]New Dummy Text New

"

While IterNo() <= SubStringCount(Comment,' : [');

hope this helps

regards

Marco

4 Replies
Highlighted

Re: Parsing fields from a string

Is this Comment-field the definitely format for this string - then in your example is no unique delimiter for the rows? If you have one it would be quite easy to split the string per subfield(string, delimiter) which split the string on the delimiter and repeated the other parts from this record. Quite similar is to use a while-loop whereby iterno() <= substringcount(delimiter). The other string-parts for the old- and new value could be split per subfield(string, delimiter, number) or textbetween() or maybe some nested mid/left/right- and index-functions.

I think if you haven't such unique delimiter you will need to do it quite similar only that you will need additionally steps within a if-loop where the current record-value with a previous record will be per peek() or previous() compared and the record-values will be adjusted. The worser the datastructure are the more efforts do you need with this comparing, checking and adjusting.

Maybe the following links are helpful in understanding what I mean:

Re: Vertical Data Text in Pivot Table

Re: Line adding ETL

- Marcus

Re: Parsing fields from a string

Hi,

one solution could be:

QlikCommunity_Thread_181465_Pic1.JPG

table1:

Generic

LOAD ID,

    SubField(SubComment,' : [',1) as FieldName,

    TextBetween(SubComment,'[',']',IterNo())&'Value' as ColNam,

    TextBetween(SubComment&'[',']','[',IterNo()) as ColVal

While IterNo() <= SubStringCount(SubComment,'[');

LOAD ID,

    Trim(Mid(Comment, Alt(Previous(Pos)+1,1),RangeSum(Pos,-Previous(Pos)))) as SubComment;

LOAD *,

    If(IterNo()=SubStringCount(Comment,' : ['),Len(Comment),Index(Left(Comment,Index(Comment,' : [',IterNo()+1)-1),' ',-1)) as Pos

INLINE "

    ID, Comment

    1, Field1 : [Old]ValueOld[New]ValueNew Field2 : [Old]Dummy Text Old [New]New Dummy Text New Field3 : [Old]Dummy Text Old [New]New Dummy Text New

"

While IterNo() <= SubStringCount(Comment,' : [');

hope this helps

regards

Marco

yura_ratu
Contributor II

Re: Parsing fields from a string

Hi Marco,

Works good, thank you

Re: Parsing fields from a string

You're welcome

Regards

Marco

Community Browser