Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Separating a field into its components and making each a column in a chart

Hi,

In a record I have a field called "geo" which includes a list of countries "au,gb,us,it" for example.

I want to create a  table which will display all records, but will "break" the 'GEO' field into its individual components and make them a column.

So for example I have a records saying "ACOUNT A | au,gb,it,us" and a record "ACCOUNT B| au,it"

So the table header will be "Account" "AU" "GB" "US" "IT"

It will show two raws -one for account A and another for B, and will have 5 columns. The first with account name and then a counter or any other indicator under the relevant GEO for the account.

Any idea if this can be done ?

Thanks

Gur

6 Replies
pauljohansson
Creator III
Creator III

Post your progress so far, then it will be easier to help out and give recommendations

/Paul

Not applicable
Author

Hi Paul,

Thanks for responding.

I am not sure what do you mean by progress.

I have the table, but the GEO field is a text string - see attached screenGEO-field-problem.png

I am looking for the best way to "break"each GEO country intoa different column.

I am importing the data from a mySQL DB, through a QVD file layer.

Thanks

Gur

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

Use SubField(countrry_codes, ',"). to break each country into a new row and combine that with a Generic load to create a column for each country.

http://qlikviewnotes.blogspot.com/2010/05/use-cases-for-generic-load.html

-Rob

http://robwunderlich.com

Not applicable
Author

Hope this helps you.

Data:

LOAD * INLINE [  

    ID, Text

    1, "ACOUNT A | au,gb,it,us"

    2, "ACCOUNT B| au,it"

];

Temp:

LOAD SubField(Text, ',') AS FieldName,

           Left(Text,Index(Text,'|')-1) as NewText

RESIDENT Data;

Final:

GENERIC LOAD '1' as Flag, FieldName,NewText Resident Temp;

Not applicable
Author

Thanks Rob. Thats seem very intresting. I am testing it and will get back to you.

Not applicable
Author

Thanks KStreak.

It probably makes my life even easier. I am testing it and will get back to you.

Thanks