Separate Client names from single field value to multiple rows

[richard24best]

Hi team,

I have requirement to create a table with Client analysis

Have the below files like

Business ID	Client Name
12345	A,B,C
57546	B,D,Z
65487	C,F,H

Want to separate Client Names and create for each client how many Business IDs are there

something like this, thanks for your time and support in advance, Richard

Client Name	Count of Business ID
A	1
B	2
C	2
D	1
F	1
H	1
Z	1

[sbaro_bd]

Hi @richard24best ,

See the attached file for solution.

Regards.

[richard24best]

Thank you @sbaro_bd for your prompt response

Well, i did try this approach to check initially.

But the problem is we have huge number of clients/business IDs so is there any more efficient way to do this ?

Thanks in advance,

Richard

[PrashantSangle]

use subfield() to separate Client Names

SubField("Client Name",',') as Client 

and in front end use filter Pane - take Client as field 

In Property of Filter Pane > presentation > Show Frequency > Frequency Count

Regards,

Prashant Sangle 

[sbaro_bd]

Hi @richard24best ,

Use a script like this :

INPUT:
LOAD
SubStringCount(ClientName,CHR(44)) AS Nb_separator,
*
INLINE [
BusinessID|ClientName
12345|A,B,C
57546|B,D,Z
65487|C,F,H
](delimiter is '|')
;

// Number of occurences , the maximum for all rows
INT:
LOAD
	Max(Nb_separator) AS Nb_separator_max
RESIDENT INPUT;

LET vLimit = Peek('Nb_separator_max',0,'INT') + 1;

// Loop the creation of different fields
FOR i=1 TO $(vLimit)
	LEFT JOIN (INPUT)
    LOAD
    	BusinessID,
        SubField(ClientName, CHR(44), $(i)) AS [Field$(i)]
    RESIDENT INPUT;
NEXT i

// Concatenate all fields ad create a new table
FOR i=1 TO $(vLimit)
	TAB:
	LOAD
    	BusinessID,
        [Field$(i)] AS Client
    RESIDENT INPUT;
NEXT i

// Table finale
FINAL_TAB:
LOAD
	Client AS ClientName,
	COUNT(BusinessID) AS [N° BusinessID]
RESIDENT TAB
GROUP BY Client
;

// Delete tables
DROP TABLE TAB, INT;

Regards.