topic Re: Comma separate values into rows in QlikView

Comma separate values into rows

Anonymous — Thu, 18 Jan 2018 21:56:51 GMT

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

Re: Comma separate values into rows

rwunderlich — Thu, 18 Jan 2018 22:23:17 GMT

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

Re: Comma separate values into rows

MarcoWedel — Thu, 18 Jan 2018 22:28:46 GMT

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

Re: Comma separate values into rows

MarcoWedel — Thu, 18 Jan 2018 22:33:17 GMT

seems I've been late ...

Re: Comma separate values into rows

Anonymous — Fri, 19 Jan 2018 04:30:25 GMT

Thanks for the help.

Re: Comma separate values into rows

Anonymous — Fri, 19 Jan 2018 04:31:34 GMT

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

Thanks for the help!

Re: Comma separate values into rows

cspindley — Thu, 14 Jan 2021 14:14:27 GMT

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