Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Post your progress so far, then it will be easier to help out and give recommendations
/Paul
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 screen
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
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
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;
Thanks Rob. Thats seem very intresting. I am testing it and will get back to you.
Thanks KStreak.
It probably makes my life even easier. I am testing it and will get back to you.
Thanks