Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

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
Not applicable

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

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

/Paul

Not applicable

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

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
Not applicable

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

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

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

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

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

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

Not applicable

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

Thanks KStreak.

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

Thanks