topic Re: How to convert comma separated values to row values in QlikView

How to convert comma separated values to row values

Fri, 19 Feb 2016 14:14:20 GMT

I have data in this format

Project ID	Firm	Year	KPI	Inventors
6417100	AMD	2002	2	05736423-1;04294002-2;04954459-1
6415975	AMD	2002	1	06193134-1;06028350-1
6400160	AMD	2002	8	06400160-1

I want in this format

Project ID	Firm	Year	KPI	Inventors
6417100	AMD	2002	2	05736423-1
6417100	AMD	2002	2	04294002-2
6417100	AMD	2002	2	04954459-1
6415975	AMD	2002	1	06193134-1
6415975	AMD	2002	1	06028350-1
6400160	AMD	2002	8	06400160-1

Also I want count of inventors like this

Project ID	Firm	Year	KPI	Inventors	Count of Inventors
6417100	AMD	2002	2	05736423-1;04294002-2;04954459-1	3
6415975	AMD	2002	1	06193134-1;06028350-1	2
6400160	AMD	2002	8	06400160-1	1

Please provide the code as I am using personal edition. Thanks in advance

Re: How to convert comma separated values to row values

sunny_talwar — Fri, 19 Feb 2016 14:15:39 GMT

Try this:

Table:

LOAD [Project ID],

Firm,

Year,

KPI,

SubField(Inventors, ';') as Inventors,

(SubStringCount(Inventors, ';') + 1) as [Count of Inventors]

FROM Source;

Re: How to convert comma separated values to row values

Fri, 19 Feb 2016 15:37:01 GMT

Can I get distinct count instead of count?

Re: How to convert comma separated values to row values

sunny_talwar — Fri, 19 Feb 2016 15:42:00 GMT

Try this:

Table:

LOAD RowNo() as RowNo,

[Project ID],

Firm,

Year,

KPI,

SubField(Inventors, ';') as Inventors;

LOAD * Inline [

Project ID, Firm, Year, KPI, Inventors

6417100, AMD, 2002, 2, 05736423-1;04294002-2;04954459-1;04954459-1

6415975, AMD, 2002, 1, 06193134-1;06028350-1

6400160, AMD, 2002, 8, 06400160-1;06400160-1

];

Left Join (Table)

LOAD [Project ID],

Count(DISTINCT Inventors) as [Count of Inventors]

Resident Table

Group By [Project ID];

Re: How to convert comma separated values to row values

Fri, 19 Feb 2016 15:46:46 GMT

So you mean there is no direct way of doing that like substringcount

Re: How to convert comma separated values to row values

sunny_talwar — Fri, 19 Feb 2016 16:07:46 GMT

I don't think SubStringCount will work for Distinct Count