conversion error with multiple if conditions

Question

Hi, Thank you so much for your time. I have the below if condition statement from the Qlikview reporting tool  as part of the migration to another tool,  iam trying to put the below IF conditions into the Alteryx workflow but i got stuck..with the conversion error..

the syntax for the general IF condition in Qlikview is IF (condtiion ,thenpart, elsepart)

If([DR2 - Last Valid Date]= 'Gap', 'No-Data',

If(Band = '1',
If([DR2 - Last Valid Date]< [SR1 - Last Valid Date] and [SR1 - Test Passed] = 'Yes' ,
[SR1 - Last Valid Date] +365 ,
If([DR2 - Test Passed] = 'Yes', [DR2 - Last Valid Date]+365 , 'No Test Date')
),

If(Band = '2',
If([DR2 - Last Valid Date]< [SR1 - Last Valid Date] and [SR1 - Test Passed] = 'Yes' ,
[SR1 - Last Valid Date] +365 ,
If([DR2 - Test Passed] = 'Yes', [DR2 - Last Valid Date]+365 , 'No Test Date'))

,
If(Band='3',
If([DR2 - Last Valid Date]< [SR1 - Last Valid Date] and [SR1 - Test Passed] = 'Yes' ,
[SR1 - Last Valid Date] +730,
IF([DR2 - Test Passed] = 'Yes',[DR2 - Last Valid Date]+730, 'No Test Date')),

If(Band='4', 'Band4')
)
)
)
)

I tried to convert this into Alteryx IF ELSEIF but iam getting the conversion error - attached the error message

could some please help - also attached some sample test data

pedrodrfaria · Answer

Hi @Raj_007

These conversion errors are just messages and they will not interfere with what you are trying to do.

It is simply because based on your function, it is trying to convert the N/A into a date, which would be impossible.

I just ran your dataset with your function and it checks out. You got no null values and you got "No-Data" for when it is supposed to be showing up

If you are just trying to avoid seeing the conversion errors, the way around it would be to remove the NA and GAP and bring them back again after the function. I added an example below for this. As you can see, no conversion errors this time.

Pedro.

conversion error with multiple if conditions.yxmd

Raj_007 · Answer

Hi Qiu,

i removed the datetimeformat and i still get the conversion error

IF [DR2 - Last Valid Date] in ('Gap','N/A') THEN 'No-Data'

//Calculation For Band1
ELSEIF
([Band Temp] ='1' and DateTimeParse([DR2 - Last Valid Date],'%m/%d/%Y') < DateTimeParse([SR1 - Last Valid Date],'%m/%d/%Y')
and [SR1 - Test Passed]='Yes' ) THEN
ToString(DateTimeAdd(DateTimeParse([SR1 - Last Valid Date], '%m/%d/%Y'), 365, 'days'))

ELSEIF [DR2 - Test Passed] = 'Yes' THEN
ToString(DateTimeAdd(DateTimeParse([DR2 - Last Valid Date], '%m/%d/%Y'), 365, 'days'))

ELSE 'No Test Date'
ENDIF

ConvError: Formula (259): DATETIMEPARSE: Cannot convert "N/A" to a date/time with format "%m/%d/%Y": Expected a number for Month: 'N/A'

Raj_007 · Answer

Hi, Qiu, are you saying i should use the DateTimeFormat? Do you think the if conditions logic is correct