is there a function to un-concat a field during data load?

[daveatkins]

example source input data:

sales person, regions

Dave, "North, West"

desired load is 2 rows:

sales person, region 

Dave, North

Dave, West

This is the opposite function of concat(region,',')

purpose is to simplify data entry spreadsheet so users do not have to create multiple rows

[edwin]

there is no unconcatenate function, the closest is subfield.  as there is no indication how many components will be needed, i suggest use combination of subfield and the cosstab feature:

data:
load person, subfield(regions,',',1) as region1, subfield(regions,',',2) as region2;
load 'Dave' as person, 'North,West' as regions
AutoGenerate(1);

newData:
//load person, Region;
crosstable(regionType, region)
load * resident data;

drop table data;
data:
load person, region resident newData;

drop table newData;

the first part is just to start the data table but you will not need that and just point to your table.

[Kushal_Chawda · Accepted solution]

@daveatkins  subfield is the function for you

load person, 
     subfield(regions,',') as region
FROM Source;

[hjimenez]

Hi @daveatkins,

Please try this:

Table1:
Load * Inline [
Sales person, regions
Dave, "North, West"
];

FinalResult:
NoConcatenate
Load
[Sales person],
SubField(regions,',') as region
Resident Table1;
Drop Table Table1;

Regards,
Hector