Split string to lines in a new dimension

[maxime66]

Hi Qlikers,

i would like to create a calculated dimension that create as many lines as characters surrounded by hooks in my string.

without script modifications ...

see screenshot in qlikview attached

any ideas ?

Thanks

[marcus_malinow]

Hi Maxime,

try this:

InitialTable:
LOAD * INLINE [
id_cust, comment
1, [shoes]] blabkqsq [caps]] jdskdg [pants]] mkk
2, [shoes]] blabkqsq
3, [caps]] jdskdg [shirts]] mkk
]
;

subfields:
LOAD
id_cust,
commentsubfield1,
'[' & right(commentsubfield1, len(commentsubfield1) - index(commentsubfield1, '[')) & ']' as commentsubfield //now split by '[' and add enclosing braces
WHERE index(commentsubfield1, '[') > 0
;
LOAD
id_cust,
subfield(comment, ']') as commentsubfield1 //first split by ']'
RESIDENT InitialTable;

[maxime66]

thanks Marcu

but i would like not modifying my script ...

[marcus_malinow]

Aah that's going to be tricky then...

Any particular reason you're averse to modifying your script?

[Anil_Babu_Samineni]

May be marcowedel‌

[maxime66]

Yess

i generally don't like to add some script if it can be solved in report (but i will do if no solution)

i only have 5 possible values fo product so

I tried to use a mix of Valuelist() et Wildmatch() , but not working

maybe it need an Aggr somewhere ...

new_dim :

= if (  Valuelist('[shoes]','[caps]','[pants]') = '[Shoes]' and wildmatch(comment,'[shoes]') ,'[Shoes]' ,

if (  Valuelist('[shoes]','[caps]','[pants]') = '[caps]' and wildmatch(comment,'[caps]) ,'[caps]' ,

...

[Kushal_Chawda · Accepted solution]

you can do something like this.

Ad you said you have 5 products you can use valueloop()

Create straight table

Dimension:

1) id_cust,

2) valueloop(1,5,1)

Expression:

1) Product:

   pick(match(ValueLoop(1,5,1),ValueLoop(1,5,1)), TextBetween(comment,'[',']',ValueLoop(1,5,1)))

2) Count:

  count(pick(match(ValueLoop(1,5,1),ValueLoop(1,5,1)), TextBetween(comment,'[',']',ValueLoop(1,5,1))))

[maxime66]

Very nice !!

It works.

Do you think we could convert  "Product" Expression in a Dimension,

keep the count expression and drop all the rest ?

regards.

[Kushal_Chawda]

This will be quite challenging. let me check on that.