Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
GELÖST

Converting Python Code to IF statement

terrellchong
Asteroid

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:

terrellchong_0-1640772784125.png

 

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!

5 ANTWORTEN 5
apathetichell
Altair

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
Asteroid

Answers to your reply:

1) It was in date in Alteryx

terrellchong_0-1640791178290.png

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

terrellchong_1-1640791198204.png

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
Altair

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
Altair

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
Asteroid

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

terrellchong_0-1640794229514.png

I separated it into 4 lines of column!

Beschriftungen
Top-Lösungs-Autoren