Alteryx Designer Desktop Discussions

awanishyadavv

Error conversion in the formula tool and limit reached.

I am not getting this error for every documents-----------------

This is my work Flow------------

This is the Formulas I am applying-------------------------------------

This is Sample data----------------

JSON_Name

result.0._id

result.0.email

result.0.lead_itineraries.0._leadId

result.0.lead_itineraries.0._tripId

result.0.lead_itineraries.0.itinerary

result.0.lead_itineraries.0.insertDate

result.0.lead_itineraries.0.startDate

result.0.lead_itineraries.0.endDate

result.0.lead_itineraries.0.confirmed

result.0.lead_itineraries.0.bookingValue

result.0.trip_itineraries.0._tripId

result.0.trip_itineraries.0.tripCreatedDate

result.0.trip_itineraries.0.inrToLocaleExchangeRate

result.0.trip_itineraries.0.lastQuotedAmountLocal

result.0.trip_itineraries.0.lastQuotedLocalCurrency

result.0.trip_itineraries.0.lastQuotedAmountCustomer

result.0.trip_itineraries.0.lastQuotedCustomerCurrency

result.0.trip_itineraries.0.usdQuotedAmount

result.0.trip_itineraries.0.customerBookingPaymentAmount

result.0.trip_itineraries.0.customerFirstPaymentAmount

result.0.trip_itineraries.0.customerSecondPaymentAmount

result.0.trip_itineraries.0.customerThirdPaymentAmount

result.0.trip_itineraries.0.customerFourthPaymentAmount

result.0.trip_itineraries.0.customerBookingPaymentCurrency

result.0.trip_itineraries.0.customerFirstPaymentCurrency

result.0.trip_itineraries.0.customerSecondPaymentCurrency

result.0.trip_itineraries.0.customerThirdPaymentCurrency

result.0.trip_itineraries.0.customerFourthPaymentCurrency

result.0.trip_itineraries.0.customerBookingPaymentDate

result.0.trip_itineraries.0.customerFirstPaymentDate

result.0.trip_itineraries.0.customerSecondPaymentDate

result.0.trip_itineraries.0.customerThirdPaymentDate

result.0.trip_itineraries.0.customerFourthPaymentDate

result.0.name

result.0.fullPhone

result.0.phone

result.0.countryCode

result.0.dialCode

result.1._id

result.1.email

result.1.lead_itineraries.0._leadId

result.1.lead_itineraries.0._tripId

result.1.lead_itineraries.0.itinerary

result.1.lead_itineraries.0.insertDate

result.1.lead_itineraries.0.startDate

result.1.lead_itineraries.0.endDate

result.1.lead_itineraries.0.confirmed

result.1.lead_itineraries.0.bookingValue

result.1.trip_itineraries.0._tripId

result.1.trip_itineraries.0.tripCreatedDate

result.1.trip_itineraries.0.inrToLocaleExchangeRate

result.1.trip_itineraries.0.lastQuotedAmountLocal

result.1.trip_itineraries.0.lastQuotedLocalCurrency

result.1.trip_itineraries.0.lastQuotedAmountCustomer

result.1.trip_itineraries.0.lastQuotedCustomerCurrency

result.1.trip_itineraries.0.usdQuotedAmount

result.1.trip_itineraries.0.customerBookingPaymentAmount

result.1.trip_itineraries.0.customerFirstPaymentAmount

result.1.trip_itineraries.0.customerSecondPaymentAmount

result.1.trip_itineraries.0.customerThirdPaymentAmount

result.1.trip_itineraries.0.customerFourthPaymentAmount

result.1.trip_itineraries.0.customerBookingPaymentCurrency

result.1.trip_itineraries.0.customerFirstPaymentCurrency

result.1.trip_itineraries.0.customerSecondPaymentCurrency

result.1.trip_itineraries.0.customerThirdPaymentCurrency

result.1.trip_itineraries.0.customerFourthPaymentCurrency

result.1.trip_itineraries.0.customerBookingPaymentDate

result.1.trip_itineraries.0.customerFirstPaymentDate

result.1.trip_itineraries.0.customerSecondPaymentDate

result.1.trip_itineraries.0.customerThirdPaymentDate

result.1.trip_itineraries.0.customerFourthPaymentDate

result.1.name

result.1.fullPhone

result.1.phone

result.1.countryCode

result.1.dialCode

result.2._id

result.2.email

result.2.lead_itineraries.0._leadId

result.2.lead_itineraries.0._tripId

result.2.lead_itineraries.0.itinerary

result.2.lead_itineraries.0.insertDate

result.2.lead_itineraries.0.startDate

result.2.lead_itineraries.0.endDate

result.2.lead_itineraries.0.confirmed

result.2.lead_itineraries.0.bookingValue

result.2.trip_itineraries.0._tripId

result.2.trip_itineraries.0.tripCreatedDate

result.2.trip_itineraries.0.inrToLocaleExchangeRate

result.2.trip_itineraries.0.lastQuotedAmountLocal

result.2.trip_itineraries.0.lastQuotedLocalCurrency

result.2.trip_itineraries.0.lastQuotedAmountCustomer

result.2.trip_itineraries.0.lastQuotedCustomerCurrency

result.2.trip_itineraries.0.usdQuotedAmount

result.2.trip_itineraries.0.customerBookingPaymentAmount

result.2.trip_itineraries.0.customerFirstPaymentAmount

result.2.trip_itineraries.0.customerSecondPaymentAmount

result.2.trip_itineraries.0.customerThirdPaymentAmount

result.2.trip_itineraries.0.customerFourthPaymentAmount

result.2.trip_itineraries.0.customerBookingPaymentCurrency

result.2.trip_itineraries.0.customerFirstPaymentCurrency

result.2.trip_itineraries.0.customerSecondPaymentCurrency

result.2.trip_itineraries.0.customerThirdPaymentCurrency

result.2.trip_itineraries.0.customerFourthPaymentCurrency

result.2.trip_itineraries.0.customerBookingPaymentDate

result.2.trip_itineraries.0.customerFirstPaymentDate

result.2.trip_itineraries.0.customerSecondPaymentDate

result.2.trip_itineraries.0.customerThirdPaymentDate

result.2.trip_itineraries.0.customerFourthPaymentDate

result.2.name

result.2.fullPhone

result.2.phone

result.2.countryCode

result.2.dialCode

result.3._id

result.3.email

result.3.lead_itineraries.0._leadId

result.3.lead_itineraries.0._tripId

result.3.lead_itineraries.0.itinerary

result.3.lead_itineraries.0.insertDate

result.3.lead_itineraries.0.startDate

result.3.lead_itineraries.0.endDate

result.3.lead_itineraries.0.confirmed

result.3.lead_itineraries.0.bookingValue

result.3.trip_itineraries.0._tripId

result.3.trip_itineraries.0.tripCreatedDate

result.3.trip_itineraries.0.inrToLocaleExchangeRate

result.3.trip_itineraries.0.lastQuotedAmountLocal

result.3.trip_itineraries.0.lastQuotedLocalCurrency

result.3.trip_itineraries.0.lastQuotedAmountCustomer

result.3.trip_itineraries.0.lastQuotedCustomerCurrency

result.3.trip_itineraries.0.usdQuotedAmount

result.3.trip_itineraries.0.customerBookingPaymentAmount

result.3.trip_itineraries.0.customerFirstPaymentAmount

result.3.trip_itineraries.0.customerSecondPaymentAmount

result.3.trip_itineraries.0.customerThirdPaymentAmount

result.3.trip_itineraries.0.customerFourthPaymentAmount

result.3.trip_itineraries.0.customerBookingPaymentCurrency

result.3.trip_itineraries.0.customerFirstPaymentCurrency

result.3.trip_itineraries.0.customerSecondPaymentCurrency

result.3.trip_itineraries.0.customerThirdPaymentCurrency

result.3.trip_itineraries.0.customerFourthPaymentCurrency

result.3.trip_itineraries.0.customerBookingPaymentDate

result.3.trip_itineraries.0.customerFirstPaymentDate

result.3.trip_itineraries.0.customerSecondPaymentDate

result.3.trip_itineraries.0.customerThirdPaymentDate

result.3.trip_itineraries.0.customerFourthPaymentDate

result.3.name

result.3.fullPhone

result.3.phone

result.3.countryCode

result.3.dialCode

And The Error I am getting is this------------------------------------

ConvError: Formula (4): TONUMBER: 0._id lost information in conversion
ConvError: Formula (4): TONUMBER: result.0._id lost information in conversion
ConvError: Formula (4): TONUMBER: result.0._id lost information in conversion
ConvError: Formula (4): TONUMBER: 0.email lost information in conversion
ConvError: Formula (4): TONUMBER: result.0.email lost information in conversion
ConvError: Formula (4): TONUMBER: result.0.email lost information in conversion
ConvError: Formula (4): TONUMBER: 0.lead_itineraries.0._leadId lost information in conversion
ConvError: Formula (4): TONUMBER: result.0.lead_itineraries.0._leadId lost information in conversion
ConvError: Formula (4): TONUMBER: 0.lead_itineraries.0._tripId lost information in conversion
ConvError: Formula (4): TONUMBER: result.0.lead_itineraries.0._tripId lost information in conversion

This is How I want the result after success ful conversions-------------

JSON_Name	JSON_ValueString	Column Index	UserGroup	LeadGroup	TripGroup
result.0._id		_id	0	0	0
result.0.email		email	0	0	0
result.0.lead_itineraries.0._leadId		_leadId	0	0	0
result.0.lead_itineraries.0._tripId		_tripId	0	0	0
result.0.lead_itineraries.0.itinerary		itinerary	0	0	0
result.0.lead_itineraries.0.insertDate		insertDate	0	0	0
result.0.lead_itineraries.0.startDate		startDate	0	0	0
result.0.lead_itineraries.0.endDate		endDate	0	0	0
result.0.lead_itineraries.0.confirmed		confirmed	0	0	0
result.0.lead_itineraries.0.bookingValue		bookingValue	0	0	0
result.0.trip_itineraries.0._tripId		_tripId	0	0	0
result.0.trip_itineraries.0.tripCreatedDate		tripCreatedDate	0	0	0
result.0.trip_itineraries.0.inrToLocaleExchangeRate		inrToLocaleExchangeRate	0	0	0
result.0.trip_itineraries.0.lastQuotedAmountLocal		lastQuotedAmountLocal	0	0	0
result.0.trip_itineraries.0.lastQuotedLocalCurrency		lastQuotedLocalCurrency	0	0	0
result.0.trip_itineraries.0.lastQuotedAmountCustomer		lastQuotedAmountCustomer	0	0	0
result.0.trip_itineraries.0.lastQuotedCustomerCurrency		lastQuotedCustomerCurrency	0	0	0
result.0.trip_itineraries.0.usdQuotedAmount		usdQuotedAmount	0	0	0
result.0.trip_itineraries.0.customerBookingPaymentAmount		customerBookingPaymentAmount	0	0	0
result.0.trip_itineraries.0.customerFirstPaymentAmount		customerFirstPaymentAmount	0	0	0
result.0.trip_itineraries.0.customerSecondPaymentAmount		customerSecondPaymentAmount	0	0	0
result.0.trip_itineraries.0.customerThirdPaymentAmount		customerThirdPaymentAmount	0	0	0
result.0.trip_itineraries.0.customerFourthPaymentAmount		customerFourthPaymentAmount	0	0	0
result.0.trip_itineraries.0.customerBookingPaymentCurrency		customerBookingPaymentCurrency	0	0	0
result.0.trip_itineraries.0.customerFirstPaymentCurrency		customerFirstPaymentCurrency	0	0	0
result.0.trip_itineraries.0.customerSecondPaymentCurrency		customerSecondPaymentCurrency	0	0	0
result.0.trip_itineraries.0.customerThirdPaymentCurrency		customerThirdPaymentCurrency	0	0	0
result.0.trip_itineraries.0.customerFourthPaymentCurrency		customerFourthPaymentCurrency	0	0	0
result.0.trip_itineraries.0.customerBookingPaymentDate		customerBookingPaymentDate	0	0	0
result.0.trip_itineraries.0.customerFirstPaymentDate		customerFirstPaymentDate	0	0	0
result.0.trip_itineraries.0.customerSecondPaymentDate		customerSecondPaymentDate	0	0	0
result.0.trip_itineraries.0.customerThirdPaymentDate		customerThirdPaymentDate	0	0	0
result.0.trip_itineraries.0.customerFourthPaymentDate		customerFourthPaymentDate	0	0	0
result.0.name		name	0	0	0
result.0.fullPhone		fullPhone	0	0	0
result.0.phone		phone	0	0	0
result.0.countryCode		countryCode	0	0	0
result.0.dialCode		dialCode	0	0	0
result.1._id		_id	1	0	0
result.1.email		email	1	0	0
result.1.lead_itineraries.0._leadId		_leadId	1	0	0
result.1.lead_itineraries.0._tripId		_tripId	1	0	0
result.1.lead_itineraries.0.itinerary		itinerary	1	0	0
result.1.lead_itineraries.0.insertDate		insertDate	1	0	0
result.1.lead_itineraries.0.startDate		startDate	1	0	0
result.1.lead_itineraries.0.endDate		endDate	1	0	0
result.1.lead_itineraries.0.confirmed		confirmed	1	0	0
result.1.lead_itineraries.0.bookingValue		bookingValue	1	0	0
result.1.trip_itineraries.0._tripId		_tripId	1	0	0
result.1.trip_itineraries.0.tripCreatedDate		tripCreatedDate	1	0	0
result.1.trip_itineraries.0.inrToLocaleExchangeRate		inrToLocaleExchangeRate	1	0	0
result.1.trip_itineraries.0.lastQuotedAmountLocal		lastQuotedAmountLocal	1	0	0
result.1.trip_itineraries.0.lastQuotedLocalCurrency		lastQuotedLocalCurrency	1	0	0
result.1.trip_itineraries.0.lastQuotedAmountCustomer		lastQuotedAmountCustomer	1	0	0
result.1.trip_itineraries.0.lastQuotedCustomerCurrency		lastQuotedCustomerCurrency	1	0	0
result.1.trip_itineraries.0.usdQuotedAmount		usdQuotedAmount	1	0	0
result.1.trip_itineraries.0.customerBookingPaymentAmount		customerBookingPaymentAmount	1	0	0
result.1.trip_itineraries.0.customerFirstPaymentAmount		customerFirstPaymentAmount	1	0	0
result.1.trip_itineraries.0.customerSecondPaymentAmount		customerSecondPaymentAmount	1	0	0
result.1.trip_itineraries.0.customerThirdPaymentAmount		customerThirdPaymentAmount	1	0	0
result.1.trip_itineraries.0.customerFourthPaymentAmount		customerFourthPaymentAmount	1	0	0
result.1.trip_itineraries.0.customerBookingPaymentCurrency		customerBookingPaymentCurrency	1	0	0
result.1.trip_itineraries.0.customerFirstPaymentCurrency		customerFirstPaymentCurrency	1	0	0
result.1.trip_itineraries.0.customerSecondPaymentCurrency		customerSecondPaymentCurrency	1	0	0
result.1.trip_itineraries.0.customerThirdPaymentCurrency		customerThirdPaymentCurrency	1	0	0
result.1.trip_itineraries.0.customerFourthPaymentCurrency		customerFourthPaymentCurrency	1	0	0
result.1.trip_itineraries.0.customerBookingPaymentDate		customerBookingPaymentDate	1	0	0
result.1.trip_itineraries.0.customerFirstPaymentDate		customerFirstPaymentDate	1	0	0
result.1.trip_itineraries.0.customerSecondPaymentDate		customerSecondPaymentDate	1	0	0
result.1.trip_itineraries.0.customerThirdPaymentDate		customerThirdPaymentDate	1	0	0
result.1.trip_itineraries.0.customerFourthPaymentDate		customerFourthPaymentDate	1	0	0
result.1.name		name	1	0	0
result.1.fullPhone		fullPhone	1	0	0
result.1.phone		phone	1	0	0
result.1.countryCode		countryCode	1	0	0
result.1.dialCode		dialCode	1	0	0
result.2._id		_id	2	0	0
result.2.email		email	2	0	0
result.2.lead_itineraries.0._leadId		_leadId	2	0	0
result.2.lead_itineraries.0._tripId		_tripId	2	0	0
result.2.lead_itineraries.0.itinerary		itinerary	2	0	0
result.2.lead_itineraries.0.insertDate		insertDate	2	0	0
result.2.lead_itineraries.0.startDate		startDate	2	0	0
result.2.lead_itineraries.0.endDate		endDate	2	0	0
result.2.lead_itineraries.0.confirmed		confirmed	2	0	0
result.2.lead_itineraries.0.bookingValue		bookingValue	2	0	0
result.2.trip_itineraries.0._tripId		_tripId	2	0	0
result.2.trip_itineraries.0.tripCreatedDate		tripCreatedDate	2	0	0
result.2.trip_itineraries.0.inrToLocaleExchangeRate		inrToLocaleExchangeRate	2	0	0
result.2.trip_itineraries.0.lastQuotedAmountLocal		lastQuotedAmountLocal	2	0	0
result.2.trip_itineraries.0.lastQuotedLocalCurrency		lastQuotedLocalCurrency	2	0	0
result.2.trip_itineraries.0.lastQuotedAmountCustomer		lastQuotedAmountCustomer	2	0	0
result.2.trip_itineraries.0.lastQuotedCustomerCurrency		lastQuotedCustomerCurrency	2	0	0
result.2.trip_itineraries.0.usdQuotedAmount		usdQuotedAmount	2	0	0
result.2.trip_itineraries.0.customerBookingPaymentAmount		customerBookingPaymentAmount	2	0	0
result.2.trip_itineraries.0.customerFirstPaymentAmount		customerFirstPaymentAmount	2	0	0
result.2.trip_itineraries.0.customerSecondPaymentAmount		customerSecondPaymentAmount	2	0	0
result.2.trip_itineraries.0.customerThirdPaymentAmount		customerThirdPaymentAmount	2	0	0
result.2.trip_itineraries.0.customerFourthPaymentAmount		customerFourthPaymentAmount	2	0	0
result.2.trip_itineraries.0.customerBookingPaymentCurrency		customerBookingPaymentCurrency	2	0	0
result.2.trip_itineraries.0.customerFirstPaymentCurrency		customerFirstPaymentCurrency	2	0	0
result.2.trip_itineraries.0.customerSecondPaymentCurrency		customerSecondPaymentCurrency	2	0	0
result.2.trip_itineraries.0.customerThirdPaymentCurrency		customerThirdPaymentCurrency	2	0	0
result.2.trip_itineraries.0.customerFourthPaymentCurrency		customerFourthPaymentCurrency	2	0	0
result.2.trip_itineraries.0.customerBookingPaymentDate		customerBookingPaymentDate	2	0	0
result.2.trip_itineraries.0.customerFirstPaymentDate		customerFirstPaymentDate	2	0	0
result.2.trip_itineraries.0.customerSecondPaymentDate		customerSecondPaymentDate	2	0	0
result.2.trip_itineraries.0.customerThirdPaymentDate		customerThirdPaymentDate	2	0	0
result.2.trip_itineraries.0.customerFourthPaymentDate		customerFourthPaymentDate	2	0	0
result.2.name		name	2	0	0
result.2.fullPhone		fullPhone	2	0	0
result.2.phone		phone	2	0	0
result.2.countryCode		countryCode	2	0	0
result.2.dialCode		dialCode	2	0	0
result.3._id		_id	3	0	0
result.3.email		email	3	0	0
result.3.lead_itineraries.0._leadId		_leadId	3	0	0
result.3.lead_itineraries.0._tripId		_tripId	3	0	0
result.3.lead_itineraries.0.itinerary		itinerary	3	0	0
result.3.lead_itineraries.0.insertDate		insertDate	3	0	0
result.3.lead_itineraries.0.startDate		startDate	3	0	0
result.3.lead_itineraries.0.endDate		endDate	3	0	0
result.3.lead_itineraries.0.confirmed		confirmed	3	0	0
result.3.lead_itineraries.0.bookingValue		bookingValue	3	0	0
result.3.trip_itineraries.0._tripId		_tripId	3	0	0
result.3.trip_itineraries.0.tripCreatedDate		tripCreatedDate	3	0	0
result.3.trip_itineraries.0.inrToLocaleExchangeRate		inrToLocaleExchangeRate	3	0	0
result.3.trip_itineraries.0.lastQuotedAmountLocal		lastQuotedAmountLocal	3	0	0
result.3.trip_itineraries.0.lastQuotedLocalCurrency		lastQuotedLocalCurrency	3	0	0
result.3.trip_itineraries.0.lastQuotedAmountCustomer		lastQuotedAmountCustomer	3	0	0
result.3.trip_itineraries.0.lastQuotedCustomerCurrency		lastQuotedCustomerCurrency	3	0	0
result.3.trip_itineraries.0.usdQuotedAmount		usdQuotedAmount	3	0	0
result.3.trip_itineraries.0.customerBookingPaymentAmount		customerBookingPaymentAmount	3	0	0
result.3.trip_itineraries.0.customerFirstPaymentAmount		customerFirstPaymentAmount	3	0	0
result.3.trip_itineraries.0.customerSecondPaymentAmount		customerSecondPaymentAmount	3	0	0
result.3.trip_itineraries.0.customerThirdPaymentAmount		customerThirdPaymentAmount	3	0	0
result.3.trip_itineraries.0.customerFourthPaymentAmount		customerFourthPaymentAmount	3	0	0
result.3.trip_itineraries.0.customerBookingPaymentCurrency		customerBookingPaymentCurrency	3	0	0
result.3.trip_itineraries.0.customerFirstPaymentCurrency		customerFirstPaymentCurrency	3	0	0
result.3.trip_itineraries.0.customerSecondPaymentCurrency		customerSecondPaymentCurrency	3	0	0
result.3.trip_itineraries.0.customerThirdPaymentCurrency		customerThirdPaymentCurrency	3	0	0
result.3.trip_itineraries.0.customerFourthPaymentCurrency		customerFourthPaymentCurrency	3	0	0
result.3.trip_itineraries.0.customerBookingPaymentDate		customerBookingPaymentDate	3	0	0
result.3.trip_itineraries.0.customerFirstPaymentDate		customerFirstPaymentDate	3	0	0
result.3.trip_itineraries.0.customerSecondPaymentDate		customerSecondPaymentDate	3	0	0
result.3.trip_itineraries.0.customerThirdPaymentDate		customerThirdPaymentDate	3	0	0
result.3.trip_itineraries.0.customerFourthPaymentDate		customerFourthPaymentDate	3	0	0

Needed Urgent help!

Thanks in advance.

BS_THE_ANALYST

@awanishyadavv the formula you are applying is applied to the entire column. Not every row will be able to be cast to a number after your string manipulation. Some rows, after you've stripped some text out will still characters besides numbers. These rows aren't going to convert as you expect.

Read the errors carefully:
ConvError: Formula (4): TONUMBER: 0._id lost information in conversion
ConvError: Formula (4): TONUMBER: result.0._id lost information in conversion
ConvError: Formula (4): TONUMBER: result.0._id lost information in conversion

How can you you turn this: 0._id to a number? Of course that won't work.

You need to use additional logic. Potentially an IF statement, IF data looks like this do this ELSEIF looks like this do this ELSE blah. The IF statement would only targets certain rows in that column depending on what part of the IF statement they fall into. There's many methods of approach.

All the best,

BS

Raj

@awanishyadavv
find the workflow attached
mark done if solved.

awanishyadavv

Yes you are right @BS_THE_ANALYST

But no of incoming rows = no of output rows, does this not mean that I am not loosing any rows ??

awanishyadavv

@Raj Accepted.

BS_THE_ANALYST

@awanishyadavv yes you're are correct. The formula tool will not act as filter. For example, it FAILS to CONVERT based off your formula for a particular row i.e. a Conversion Error. This isn't filtered out, it is still passed through the tool. It'll likely just say "null or empty" as a result. i.e. it couldn't convert.

All the best,

BS

awanishyadavv

@BS_THE_ANALYST Your first suggested way will also work. I tried, and it is working.

Thanks for the help.

Alteryx Designer Desktop Discussions

Conversion Error Limit Reached