Null Values in input data
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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!
- Labels:
- Data Investigation
- Documentation
- Input
- Workflow
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Agree with @cjaneczko , we need to first cleansing the data to real numerical values.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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 ?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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 :(
