Null Values in input data

Hi All,

I would like to ask you for help. I need to create workflow and I need to use excel report downloaded from external program.
I have three columns with numbers: Invoice Amount, Open Amount and Open Amount in DC. I have problem only with Open Amount.

For better understanding I attached Metadata for those columns and image where you can find problem (Null values)

Each of those rows have value in excel file. Below I attached example of numbers which are replaced with null:

This problem already occurs in the input data step. Changing the data type in select and using the replace formula do not have affect the display of data (I tried). The process will be automatic, manual changes in Excel are not possible.

How to resolve this problem?

I will be grateful for your advices. Thank you in advance!

Workflow

Input

Data Investigation

Documentation

Accepted answers

All comments

cjaneczko

Because of the commas in the data the fields are actually strings. You will need to remove the commas with a formula tool before you go and convert the fields to Double. This is also indicated in your excel file by all of the Green flags next to each of the "numbers" which are actually text fields.

Qiu

Agree with @cjaneczko , we need to first cleansing the data to real numerical values.

Magdallenaq

Thanks for your solution but I tried to make it using formulas :Replace([Open Amount]),",", ""), ToNumber(Replace(ToString([Open Amount]),",", "")), Replace(ToString([Open Amount]),",", "")) and this not help. I still have a null places. Could you please help me witch formula I shoud to use? Thanks in advance!

binu_acs

@Magdallenaq Can you copy your input data to a new excel file and try open in alteryx and check how the values are displaying in alteryx?

Magdallenaq

I tried and its works. But unfortunately I cannot make it in this way - as I mentioned this process should be totally automatically. I tried to download new report from the system and is still the same problem.. Do you have any idea why? Thanks a lot for all replies!

binu_acs

@Magdallenaq I think the issue with the report system generating excel file has some compatible issues. you need to have some fix in the input file then

Magdallenaq

I notice that report is saved automatically as Excel 1997-2003. After manually save as Excel worbook everything is ok. Is any option to avoid manually saved data as Excel workbook ?

binu_acs

@Magdallenaq one option convert the files in to .xlsx then process . Can you try the below method to see your original output can saved into .xlsx and process?

https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Discussions/Microsoft-Excel-97-2003-Worksheets-to-xlsx-files/td-p/1214976

Magdallenaq

Thanks for your reply but this case is little different - This case is related to output, I need to convert input data. After all replies I think that is not possible without manually saving

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703