Work with a string with variable length

[#1]

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

Audi, BMW

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

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

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

Fiat, Saab

Fiat, Saab

Fiat, Saab

Fiat, Saab

Mercedes, Volvo, Audi

Renault

Saab, Volvo

Volvo, Ford, Renault

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

[sunny_talwar · Accepted solution]

May be like this:

Table:

LOAD RecNo() as SNo,

  *,

  SubField(ConcatList, ', ') as List;

LOAD * Inline [

ConcatList

Audi

Audi, BMW

Audi, BMW

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

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

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

Fiat, Saab

Fiat, Saab

Fiat, Saab

Fiat, Saab

Mercedes, Volvo, Audi

Renault

Saab, Volvo

Volvo, Ford, Renault

Volvo, Ford, Renault

Volvo

] (delimiter is |);

[#3]

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

[#4]

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

[sunny_talwar]

This:

Table:

LOAD RecNo() as SNo,

  *,

  SubField(A, ', ') as List;

LOAD A

FROM

Inline_data_in_XLS.xlsx

(ooxml, no labels, table is Sheet1);