Field calculator Script for merging fields with conditions.

821
4
04-10-2019 09:42 AM
VukCeperkovic1
New Contributor

I need help with writing VBA or python script that will merge fields in way I did it in excel (see formula applied on red column in attached spreadsheet).

Basically merging fields but with some conditions implemented.

0 Kudos
4 Replies
DanPatterson_Retired
MVP Emeritus

security toasted your vba macro I presume.  So just in case you needed to check for nulls, here is the principle behind it.  It will produce a concatenated text string for three fields 

def conc(a, b, c):
    vals = [i if i else i for i in [a, b, c] ]
    txt = "{}{}{}".format(a, b, c)
    return txt



conc(1000, None, 'a')
Out[19]: '1000Nonea'

conc(1000, 'a', 5)
Out[20]: '1000a5'
JoshuaBixby
MVP Esteemed Contributor

Try the following expression using the Python parser:

"{}0{}".format(!odeljenje!,!odesk!.replace(" ",""))
VukCeperkovic1
New Contributor

Thanks on replies everyone..

I am novice to  any type of scripting so I don't get it what to type in field calculator.. 

I need script which will populate new field, make concatenation of three fields (GJ, Odeljenje, Odsek) with couple conditions in field Odeljenje (test length of the content) and in field Odsek (test is the content number and length of it and if it is not change the case of text),

basically translate excel formula

CONCATENATE(A2,IF(LEN(B2)=1,CONCATENATE("00",B2),IF(LEN(B2)=2,CONCATENATE("0",B2),B2)),IF(ISNUMBER(C2+1),IF(LEN(C2)<2,CONCATENATE("0",C2),C2),LOWER(C2)))

to script (either with python or vba).

0 Kudos
DanPatterson_Retired
MVP Emeritus

My guess, because I can't load the spreadsheet because of the embedded vba and just going by your screen grab of the perfect output. Here are 2 examples


"{:>04.0f}{:>03.0f}{}".format(11,2,'z'.lower())
'0011002z'

"{:>04.0f}{:>03.0f}{}".format(1009,17,'A'.lower())
'1009017a'‍‍‍‍‍‍‍‍‍‍

Which would suggest that your field calculator expression using a python parser with fields named

!A!, !B!, !C! would be

"{:>04.0f}{:>03.0f}{}".format(!A!, !B!, !C!.lower())

This translates to

{:>04.0f}  means 4 character spaces with 0 padding, hence if the width of the number is 2, you  get 2 padded 0's

{:>03.0f}  same as above, but a 3 character space with 0 padding.

{}                 means, just a string representation

The ! marks are thrown around the field name if using a python parser

So the 2nd example block is the field calculator expression when you replace A, B, C with the actual field names, and you are calculating into a new text field 

0 Kudos