Converting Python Code to IF statement

[terrellchong]

Hi all,

I have a Python Code that needs to be decoded into alteryx workflow.

Below are the python code from Data360:-

if fields['START_DATE'] != Null and fields['EXPIRY_DATE'] != Null and fields['TRANDATE'] != Null:
    
    if fields['TRANDATE'] <= fields['START_DATE']:
        Year_ZERNYR = int(fields['START_DATE'].strftime("%Y"))
    elif fields['TRANDATE'] > fields['START_DATE']:
        Year_ZERNYR = int(fields['TRANDATE'].strftime("%Y"))  
        
    
    if (fields['SACSTYP'] == 'PS' or fields['SACSTYP'] == 'CS'):
        out1.ZERNYR = Year_ZERNYR
    elif (fields['SACSTYP'] == 'PE' or fields['SACSTYP'] == 'CE'):
        out1.ZERNYR = int(fields['EXPIRY_DATE'].strftime("%Y"))
    
    
    for i in range(2,16):
        if fields['SACSTYP'] == 'P'+str(i) or fields['SACSTYP'] == 'C'+str(i):
            out1.ZERNYR = Year_ZERNYR +i -1

This is what I had come out with:

IF !IsNull([START_DATE]) AND !IsNull([EXPIRY_DATE]) AND !IsNull([TRANDATE]) 
THEN
IF [TRANDATE]<=[START_DATE] 
THEN tonumber(datetimeformat(datetimeparse([START_DATE],"%Y-%m-%d"),"%Y")) 
ELSEIF [TRANDATE]>[START_DATE]
THEN tonumber(datetimeformat(datetimeparse([TRANDATE],"%Y-%m-%d"),"%Y"))
ELSEIF [SACSTYP]="PS" OR [SACSTYP]="CS"
THEN [ZERNYR]
ELSEIF [SACSTYP]="PE" OR [SACSTYP]="CE"
THEN tonumber(datetimeformat(datetimeparse([EXPIRY_DATE],"%Y-%m-%d"),"%Y"))
ELSEIF [SACSTYP]="P2" OR [SACSTYP]="C2"
THEN [ZERNYR] 
ELSEIF [SACSTYP]="P3" OR [SACSTYP]="C3"
THEN [ZERNYR]+1
ELSEIF [SACSTYP]="P4" OR [SACSTYP]="C4"
THEN [ZERNYR]+2
ELSEIF [SACSTYP]="P5" OR [SACSTYP]="C5"
THEN [ZERNYR]+3
ELSEIF [SACSTYP]="P6" OR [SACSTYP]="C6"
THEN [ZERNYR]+4
ELSEIF [SACSTYP]="P7" OR [SACSTYP]="C7"
THEN [ZERNYR]+5
ELSEIF [SACSTYP]="P8" OR [SACSTYP]="C8"
THEN [ZERNYR]+6
ELSEIF [SACSTYP]="P9" OR [SACSTYP]="C9"
THEN [ZERNYR]+7
ELSEIF [SACSTYP]="P10" OR [SACSTYP]="C10"
THEN [ZERNYR]+8
ELSEIF [SACSTYP]="P11" OR [SACSTYP]="C11"
THEN [ZERNYR]+9
ELSEIF [SACSTYP]="P12" OR [SACSTYP]="C12"
THEN [ZERNYR]+10
ELSEIF [SACSTYP]="P13" OR [SACSTYP]="C13"
THEN [ZERNYR]+11
ELSEIF [SACSTYP]="P14" OR [SACSTYP]="C14"
THEN [ZERNYR]+12
ELSEIF [SACSTYP]="P15" OR [SACSTYP]="C15"
THEN [ZERNYR]+13
ELSE 0 ENDIF
ELSE 0 ENDIF

Please let me know if there is anything I misunderstand about these code and I would be happy to learn from this. Thanks!

[apathetichell]

A bunch of problems:

1) you are comparing dates as strings but you have not already converted them to dates.

2) your date format is %m/%d/%Y - not %Y-%m-%d for your datetimeparse

3) zernyr has no value but you add to it as though it does.

4) your entire last section could be re-written as:

tonumber(regex_replace([SACSTYP],"^\w{1}","",0))-2

5) It's easier to test your expression with one endif - so create the first value as the test for if all 3 are nulls (ie if all 3 are nulls then 0 else...

swap about the bottom part for the regex and it could be easier to troubleshoot on your end.

[terrellchong]

Answers to your reply:

1) It was in date in Alteryx

2) I write the datetimeparse as the data shown in my table is shown as in screenshot

3) do correct my understanding: If the "IF THEN ELSE ENDIF" statement was not finish, there will be no value in ZERNYR?

4) Thanks! I always wonder if I can simplify the process!
5) Understood. As the ENDIF part also confuses me.

[apathetichell]

Hi for 1- and 2 - that looks good - and it just means you don't need to use datetimeparse. The .csv doesn't have it in that format so it has to  be converted into dates first.

for 3) This first and last part of this section says give me the original value of [zernyr] if true - but there is no original value of [zernyr] - it doesn't exist yet. So maybe instead of [Zernyr] use 0?

THEN tonumber(datetimeformat(datetimeparse([TRANDATE],"%Y-%m-%d"),"%Y"))
ELSEIF [SACSTYP]="PS" OR [SACSTYP]="CS"
THEN [ZERNYR]
ELSEIF [SACSTYP]="PE" OR [SACSTYP]="CE"
THEN tonumber(datetimeformat(datetimeparse([EXPIRY_DATE],"%Y-%m-%d"),"%Y"))
ELSEIF [SACSTYP]="P2" OR [SACSTYP]="C2"
THEN [ZERNYR] 

[apathetichell · Accepted solution]

I re-wrote your formula as:

IF IsNull([START_DATE]) AND IsNull([EXPIRY_DATE]) AND IsNull([TRANDATE])
then 0 elseIF [TRANDATE]<=[START_DATE]
THEN tonumber(datetimeformat([START_DATE],"%Y"))
ELSEIF [TRANDATE]>[START_DATE]
THEN tonumber(datetimeformat([TRANDATE],"%Y"))
ELSEIF [SACSTYP]="PS" OR [SACSTYP]="CS"
THEN 0
ELSEIF [SACSTYP]="PE" OR [SACSTYP]="CE"
THEN tonumber(datetimeformat([EXPIRY_DATE],"%Y"))
ELSE tonumber(regex_replace([SACSTYP],"^\w{1}","",0))-2
ENDIF

But I'm not quite sure if that's fully accurate. you can run with that and compare it to what you'd expect.

[terrellchong]

After about an hour digging in, i got my solution!

I separated it into 4 lines of column!