Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Try this:
Table:
LOAD [Project ID],
Firm,
Year,
KPI,
SubField(Inventors, ';') as Inventors,
(SubStringCount(Inventors, ';') + 1) as [Count of Inventors]
FROM Source;
Try this:
Table:
LOAD [Project ID],
Firm,
Year,
KPI,
SubField(Inventors, ';') as Inventors,
(SubStringCount(Inventors, ';') + 1) as [Count of Inventors]
FROM Source;
Can I get distinct count instead of count?
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];
So you mean there is no direct way of doing that like substringcount
I don't think SubStringCount will work for Distinct Count