Comma separate values into rows

[Anonymous]

Hi

I have a field in the database which has comma separated values

Ex : ID     TYPE

       1       1,2,3,4

        2       3,4,5

where TYPE

           1= USA, 2=FRANCE, 3= SPAIN, 4=UAE, 5=INDIA (like this I have 15 values )

I need the result as following:

ID    TYPE

1     USA

1     FRANCE

1     SPAIN

1     UAE

2     SPAIN

2     UAE

2    INDIA

[rwunderlich · Accepted solution]

First load a mapping table of the values to translate numeric to the text.  Next load the file using subfield() to generate a row for each comma separated type.

TypeMap:     // Could also load from a DB table if it exists

Mapping Load * INLINE [

from, to

1, USA

2, FRANCE

3, SPAIN

etc

]

;

MAP TYPE Using TypeMap;

Data:

LOAD ID,

     SubField(TYPE, ',') as TYPE

;

SQL SELECT * FROM mytable;

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

[MarcoWedel]

Hi,

one solution might be:

mapType:
Mapping LOAD * INLINE [
    In, Out
    1, USA
    2, FRANCE
    3, SPAIN
    4, UAE
    5, INDIA   
];

tabType:
LOAD ID,
    ApplyMap('mapType',SubField(TYPE,',')) as TYPE
Inline [
ID TYPE
1 1,2,3,4
2 3,4,5
] (delimiter is '\t');

hope this helps

regards

Marco

[MarcoWedel]

seems I've been late ...

[Anonymous]

Thanks for the help.

[Anonymous]

I think this will work as well, but i used the previous solution.

Thanks for the help!

[cspindley]

take care ... the solutions provided are not maintenance free