Building complex formula

[Kavya432]

Hi Team,

I have a requirement

In the attached work flow, I need help writing a formula

1) If the producer_No is 10 characters long, check if 3rd character is a '-'(dash), if its a dash remove that or else keep the producer no

example:

               10 digit producer_No(03-0439658) output need as 030439658

2) If producer_No is 9 digit or character long then take left two digits and concatenate with two zeros('00') and concatenate those with right 5 digits.

example:

              9 digit Producer_No (320937827)  output needed as 320037825 (32+00+37827)

3) If producer_No is 5 or less characters or digits add zeros to the left to make it 8 characters

    example:

                 5 or less (06150) output needed as 00006150

4)IF agency_Code is blank  fill with Producer_No if it has any.

Thanks

Kavya

[rafalolbert · Accepted solution]

 Hi @Kavya432,

The following 2x formulas should do the trick:

IF Length([producer])=10 THEN
(IF Substring([producer],2,1) = '-' THEN Left([producer], 2) + Right([producer], 7) ELSE '' ENDIF)
ELSEIF Length([producer])=9 THEN
(Left([producer], 2) + '00' + Right([producer], 5))
ELSEIF Length([producer])<=5 THEN
PadLeft([producer], 8, '0')
ELSE ''
ENDIF

and 

If IsNull([agency_code]) THEN [producer_new] ELSE [agency_code] ENDIF

Also please see attached workflow.

Thanks,

Rafal

#Excuse me, do you speak Alteryx?

[rafalolbert]

I also suggest to remove your workflow as it contains some connection strings, which won't work for anyone outside of your organisation, but potentially disclose too much detail. 🙂