topic Re: Work with a string with variable length in QlikView

Work with a string with variable length

Wed, 25 Nov 2020 16:16:04 GMT

Hello community,

I have a long list of person attributes and the car types they drove in their lives.

Sample (with 'comma & space' as separator):

Audi

Audi, BMW

BMW, Mercedes, Mazda, Toyota, Audi, Saab, Toyota

Fiat, Saab

Mercedes, Volvo, Audi

Renault

Saab, Volvo

Volvo, Ford, Renault

Volvo

I want to create a pie chart (and table) which would find all distinct types from these strings and count/sum them.

So the result should be something that give me the possibility to read:

Audi 7

BMW 5

Mercedes: 4

...

Anyone who can help me how to deal with these strings and get the 'types' out of them for processing? Now I can fix it with a lot of Excel manual manipulation, but it will not give me the ability to select on demographic attribute from the persons (car owners).

Any help is highly appreciated!

Alex

Re: Work with a string with variable length

sunny_talwar — Thu, 19 May 2016 13:09:07 GMT

May be like this:

Table:

LOAD RecNo() as SNo,

SubField(ConcatList, ', ') as List;

LOAD * Inline [

ConcatList

Audi

Audi, BMW

BMW, Mercedes, Mazda, Toyota, Audi, Saab, Toyota

Fiat, Saab

Mercedes, Volvo, Audi

Renault

Saab, Volvo

Volvo, Ford, Renault

Volvo

] (delimiter is |);

Re: Work with a string with variable length

Thu, 19 May 2016 13:12:31 GMT

WOW Sunnny, again... FASTEST Reply!!!

Thank you for enclosing the QVW file. I will start exploring your work. Thank you very much for your time! Highly appreciated. As always!

Alex

Re: Work with a string with variable length

Thu, 19 May 2016 13:23:47 GMT

Sunny, since you are on extraterrestrial level in QV, could I bother you with only one more request?

Please find the 'inline' data now as an XLSX file. How would the script work when the source is not inline but from an external source? It would help me but perhaps also other community members. 🙂

Alex

Re: Work with a string with variable length

sunny_talwar — Thu, 19 May 2016 14:00:32 GMT

This:

Table:

LOAD RecNo() as SNo,

SubField(A, ', ') as List;

LOAD A

FROM

Inline_data_in_XLS.xlsx

(ooxml, no labels, table is Sheet1);