topic Re: Split string to lines in a new dimension in QlikView

Split string to lines in a new dimension

maxime66 — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Split string to lines in a new dimension

marcus_malinow — Wed, 14 Jun 2017 08:53:04 GMT

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;

Re: Split string to lines in a new dimension

maxime66 — Wed, 14 Jun 2017 09:04:35 GMT

thanks Marcu

but i would like not modifying my script ...

Re: Split string to lines in a new dimension

marcus_malinow — Wed, 14 Jun 2017 09:13:10 GMT

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

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

Re: Split string to lines in a new dimension

Anil_Babu_Samineni — Wed, 14 Jun 2017 09:30:05 GMT

May be marcowedel‌

Re: Split string to lines in a new dimension

maxime66 — Wed, 14 Jun 2017 09:45:13 GMT

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]' ,

...

Re: Split string to lines in a new dimension

Kushal_Chawda — Wed, 14 Jun 2017 10:32:15 GMT

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))))

Re: Split string to lines in a new dimension

maxime66 — Thu, 15 Jun 2017 13:45:58 GMT

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.

Re: Split string to lines in a new dimension

Kushal_Chawda — Thu, 15 Jun 2017 13:56:11 GMT

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