Script challenge ;)

[blunckc1]

Ok folks, I am being 100% lazy, can some write the script needed to achieve the following?

I have data in 5 separate columns that need to be combined into one, the kicker being that my users don't follow the logic in how that data SHOULD be entered resulting in some columns containing nulls with others containing values.

Here are the 5 columns, with the needed transformation because of how the data comes in from SharePoint:

	SubField(ows_Other_x0020_Contributor_x0020_1,'#',-1)						as [Other Contributor 1],
	SubField(ows_Other_x0020_Contributor_x0020_2,'#',-1)						as [Other Contributor 2],
	SubField(ows_Other_x0020_Contributor_x0020_3,'#',-1)						as [Other Contributor 3],
	SubField(ows_Other_x0020_Contributor_x0020_4,'#',-1)						as [Other Contributor 4],
	SubField(ows_Other_x0020_Contributor_x0020_5,'#',-1)						as [Other Contributor 5],

Result is a new column being created called [Other contributors combined].

If there are values in columns other contributor 1 (bob), 3 (terry) and 4 (sam), the result would be bob; terry; sam.

If there are values in column other contributor 4 (jack) and 5 (phil), the result would be jack; phil.

If there is only a values in column other contributor 1 (jack), the result would be jack.

If there are no values in any of the columns, the result would be  . That is to represent a null return.

Thanks in advance!

[olivierrobin · Accepted solution]

hello

maybe something like that (you should adapt it to your requirements)

t:

load * inline [

a,b,c,d

a,b,c,d

a,b,c

a,b

a

,b

,b,c

,b,c,d

,,c,d

]

;

set s=if(len(trim($1))>0,$1 & ',');

t2:

load

left($(s(a)) & $(s(b)) &$(s(c)) & $(s(d)),len($(s(a)) & $(s(b)) &$(s(c)) & $(s(d)))-1) as z

resident t;

exit script;

[tamilarasu]

Hi Carl,

You can also try like below

Map:
Mapping Load 
Repeat(' ', RecNo()), ';'
AutoGenerate 4;
 
Data:
Load *, MapSubString('MAP', Trim(A & ' ' & B & ' ' & C & ' ' & D & ' ' & E)) as NewField;
LOAD * INLINE [
    A,B,C,D,E
    1,2,3,4,5
    1,,2,3,4
    ,,,,1
    ,,2
    ,,,5
    1
    3,,,,4
];

[blunckc1]

Thanks mate - this worked as expected!

[blunckc1]

Thanks Tamil - tried this but was getting this error

Error in expression:

MapSubString function takes a constant map as first parameter.

Olivier's solution worked so I'm all good.

[tamilarasu]

Hi Carl,

It was my fault. I just typed the table name in caps by mistake. Thought of sharing the correct solution. Have a fantastic day!

MapSubString('MAP', Trim(A & ' ' & B & ' ' & C & ' ' & D & ' ' & E))

MapSubString('Map', Trim(A & ' ' & B & ' ' & C & ' ' & D & ' ' & E))