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
You are using "DateTimeFormat" then compares.
DateTimeFormat will convert a DateTime type data into a string, ending up that you can do any comparing operation.
Maybe you should try "DateTimeParse".
If you could provide a Sample, we can take a more close look.
Hi, Qiu, are you saying i should use the DateTimeFormat? Do you think the if conditions logic is correct
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'
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.