Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

conversion error with multiple if conditions

Raj_007
8 - Asteroid

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

 

 

 

ErrorScreenshot.png

 

 

 

 

5 REPLIES 5
Raj_007
8 - Asteroid

attached some sample data

Qiu
20 - Arcturus
20 - Arcturus

@Raj_007 

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.

 

Raj_007
8 - Asteroid

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

Raj_007
8 - Asteroid

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'

pedrodrfaria
13 - Pulsar

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

 

pedrodrfaria_0-1614826824342.png

 

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.

 

pedrodrfaria_1-1614827232990.png

 

Pedro.

 

 

 

 

Labels