This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
I have the current workflow seen below, and need to (1) filter out observations with missing values for specific variables.
Any observations with missing values for: PROA, LnAT, ForeignSales, Leverage, PP&E, R&D, NOL, DeltaNOL, Haven, ETR, and Cash ETR should be removed.
(2)After that is completed (after deleting missing values), any ETR values below zero need to be reset to "0"; any values above one should be reset to "1". Same goes for CashETR.
I've tried using the DataCleansing tool to filter out missing observations for each of the 11 variables, but it doesn't seem to be working. I've tried checking rows alone, columns alone, and both...I'm fairly new to Designer still, so I'm sure there's a way to do it, I'm just not really sure what that is.
I attached the Excel base data and my current workflow below. I also attached a screenshot of some examples (not all) of highlighted observations that should be removed in step (1).
They ought to be... I downloaded the file I uploaded from the original post, and they still show up on my end. They're highlighted in the picture below, this Formula tool is what created all of them. I can provide formulas if needed, you can see from the few in this screenshot that they aren't too complex. They should be there though, not sure why they wouldn't be...
Ok, this is a simple matter of transposing the data around a RecordID, filtering any that have null value, then joining that record ID field back to the original stream. Take the left from the Join and these are the records with no missing values. The values on the join are the ones that have missing values.
I think this worked... not 100% sure, but I think it did. Is there a way to change the ETR and CashETR fields to integers while keeping the same number of decimals? I need to compute Mean, Median, and Standard Deviation of both, but when I try to change the fields' data types via a Select tool, it shortens the numbers drastically, as if it's rounding. I need to compute the Mn/Md/SD with the decimals it lists...I know I can't necessarily do this when they are formatted as a string.
That isn't working how I need it to - it keeps giving me an error that it is too big for the precision, or something like that. Or, if it doesn't give me an error, some of the ETR and Cash ETR values go [Null] after running the workflow.
It works for me without errors, so I'm not sure what you're doing differently.
This simply isolates the fields you want to check for null values, creates a row ID, pivots the data so those values are in a single column, then find those null values. Those null values will have a row id, which is used to join back and remove those rows. Simple.
If you are using data that is not the same as what you sent me then that might explain what has happened, so if you want to send something that is exactly representative of the data then I can take another look.