topic Re: is there a function to un-concat a field during data load? in App Development

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

daveatkins — Mon, 05 Jun 2023 17:33:01 GMT

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

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

edwin — Mon, 05 Jun 2023 18:39:44 GMT

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.

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

Kushal_Chawda — Mon, 05 Jun 2023 22:33:06 GMT

@daveatkins subfield is the function for you

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

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

hjimenez — Mon, 05 Jun 2023 23:11:40 GMT

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