Deleting Observations with Missing Values for Specific Variables

[AlexAlbro12]

Hello,

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).

[mceleavey]

Hi @AlexAlbro12 ,

I might be missing something but none of those fields you've listed exist in the data you've provided:

M.

[AlexAlbro12]

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...

[mceleavey]

Ah, you're creating them later.

No problem, I'll post the solution in a minute 🙂

[mceleavey · Accepted solution]

@AlexAlbro12 ,

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.

The workflow looks like this:

And the results are as follows:

Hope this helps,

M.

[AlexAlbro12]

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.

[mceleavey]

@AlexAlbro12 ,

Yeah, just change the type in a select tool to Fixed Decimal 19.2 (19 before the decimal, two after).

M.

[AlexAlbro12]

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.

[mceleavey]

@AlexAlbro12 ,

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.

M.

[AlexAlbro12]

I'm using the same data, I meant that the Fixed Decimal setting with "19.2" wasn't giving me what I needed. I will send you a p/m.