Split the concatenated field

[Anonymous]

Hi All,

I have certain codes where for each record it is separated by comma.

For eg:

Record            Code

Q123                R01

Q234                 R01,R02

Q345                 R02,D03

Q432                 D01,R01,R03

Now I need to get a list of all the codes.

Like this:

R01

R02

R03

D01

When I tried Subfield in the script I was getting multiple records for each record

Like this:

Q123            R01

Q234            R01

Q234            R02 etc..

I want distinct list of Codes without duplicating the records.

Any help is appreciated. Kindly let me know if the requirement is unclear.

Thanks,

Anupama Jagan

[sunny_talwar · Accepted solution]

May be with a link table... there will be some duplication, but it will be in a separate table and your fact table won't duplicate anything

Table:

LOAD * INLINE [

    Record, Code

    Q123,                R01

    Q234,                "R01,R02"

    Q345,                "R02,D03"

    Q432,                "D01,R01,R03"

];

LinkTable:

LOAD DISTINCT Code,

SubField(Code, ',') as SubCode

Resident Table;

[its_anandrjs]

Try only subfield but  you get multiple records because it is refereed to your Record field.

Load *subfield(Code,',') as NewCode;

Load * inline

[

Record,             Code

Q123   ,              R01

Q234   ,              "R01,R02"

Q345   ,              "R02,D03"

Q432   ,              "D01,R01,R03"

];

[YoussefBelloum]

Hi Anupama,

Try this on the script,

TextBetween(Code,'',',',1) as Code on the first load and load a second table with a resident without loading "record" column if you want only the distinct list of Codes

PS: subfiels also should work.

Regards,

Youssef

[its_anandrjs]

After all this Output you get

Q123   ,              R01

Q234   ,              R01

Q234   ,              R02

Q345   ,              R02

Q345   ,              D03

Q432   ,              D01

Q432   ,              R01

Q432   ,              R03

[Anonymous]

Thanks a lot Sunny. It worked fine

Regards,

Anupama Jagan