Data Cleansing Tool -> Delete Null Rows & Columns

[r4upadhye]

There are few workarounds for this task, but it would be really very easy if Data Cleansing Tool could delete Null Rows and Null Columns. After all its just a macro which can be modified and re-packaged into Alteryx Designer.

Currently, to delete a null row requires multiple columns validation for common Null attributes,

similarly to delete a null column every column has to be compared on a row-level and flagged for removal. Both of these approaches are clumsy.

Wouldn't it be so simple if Data Cleansing Tool gave such check boxes !!!

[r4upadhye]

@venkat05  nobody can, as of yet...

its just an open proposal

[KylieF]

Thank you for your feedback and idea! Our product team was able to revisit this idea and include it in a upcoming release! We'll update this idea again once the release is available for download.

[r4upadhye]

That's Wonderful !!!
Thank you

[Desa19]

Thats some real good feedback!

Thanks for the efforts.

[wade12]

nice work.  thanks.

[KylieF]

Thank you for your feedback and idea!

Delete Null Rows and Delete Null Columns has been added to our Data Cleansing tool in the latest release, 2020.2, which you can download here! Please note there is a order or operations when for these features where the tool will check and delete null rows before null columns should both be checked. Be sure to let us know what you think in the Designer Discussions board!

[rajkrovvidi]

Hi,

Does this only remove if all the rows are null ? How to delete the entire row when of the cell is empty ?

[r4upadhye]

@rajkrovvidi 

in general the answer is yes, it should drop empty as well as null rows & columns,

however in certain use cases (ex: dealing with specific databases etc) the tool may behave differently.

can u share more details on ur scenario or workflow ? 

[rajkrovvidi]

Hi,

I should see 5 rows but I see 6 rows. 

<?xml version="1.0"?>
<AlteryxDocument yxmdVer="2020.3">
  <Nodes>
    <Node ToolID="1">
      <GuiSettings Plugin="AlteryxBasePluginsGui.TextInput.TextInput">
        <Position x="114" y="126" />
      </GuiSettings>
      <Properties>
        <Configuration>
          <NumRows value="7" />
          <Fields>
            <Field name="CustomerID" />
            <Field name="FirstName" />
            <Field name="LastName" />
            <Field name="Term" />
            <Field name="JoinDate" />
            <Field name="Region" />
            <Field name="Score" />
            <Field name="FirstPurchaseDate" />
          </Fields>
          <Data>
            <r>
              <c>49</c>
              <c>Thomas</c>
              <c>Edison</c>
              <c>12 months</c>
              <c>2014-08-21</c>
              <c>South</c>
              <c>2</c>
              <c>2014-08-21</c>
            </r>
            <r>
              <c>456</c>
              <c><![CDATA[Galileo ]]></c>
              <c />
              <c>12 months</c>
              <c>2014-04-01</c>
              <c>South</c>
              <c />
              <c>2014-04-01</c>
            </r>
            <r>
              <c>31</c>
              <c>Benjamin</c>
              <c>Franklin</c>
              <c>36 months</c>
              <c>2014-12-21</c>
              <c>Midwest</c>
              <c>1</c>
              <c>2014-12-21</c>
            </r>
            <r>
              <c>5</c>
              <c>Dorothy</c>
              <c>Gerber</c>
              <c>12 months</c>
              <c>2014-10-01</c>
              <c>Northeast</c>
              <c>22</c>
              <c>2012-05-05</c>
            </r>
            <r>
              <c>1</c>
              <c>Louis</c>
              <c><![CDATA[ Pasteur]]></c>
              <c>60 months</c>
              <c>2013-09-06</c>
              <c>West</c>
              <c>4</c>
              <c>2013-09-06</c>
            </r>
            <r>
              <c />
              <c />
              <c />
              <c />
              <c />
              <c />
              <c />
              <c />
            </r>
            <r>
              <c>2</c>
              <c>Tim</c>
              <c>Berners-Lee</c>
              <c>36 months</c>
              <c>2014-01-26</c>
              <c><![CDATA[ Midwest]]></c>
              <c>5</c>
              <c>2014-01-26</c>
            </r>
          </Data>
        </Configuration>
        <Annotation DisplayMode="0">
          <Name />
          <DefaultAnnotationText />
          <Left value="False" />
        </Annotation>
      </Properties>
      <EngineSettings EngineDll="AlteryxBasePluginsEngine.dll" EngineDllEntryPoint="AlteryxTextInput" />
    </Node>
    <Node ToolID="2">
      <GuiSettings>
        <Position x="234" y="126" />
      </GuiSettings>
      <Properties>
        <Configuration>
          <Value name="Check Box (135)">True</Value>
          <Value name="Check Box (136)">False</Value>
          <Value name="List Box (11)">"CustomerID","FirstName","LastName","Term","JoinDate","Region","Score","FirstPurchaseDate"</Value>
          <Value name="Check Box (84)">False</Value>
          <Value name="Check Box (117)">False</Value>
          <Value name="Check Box (15)">True</Value>
          <Value name="Check Box (109)">False</Value>
          <Value name="Check Box (122)">False</Value>
          <Value name="Check Box (53)">False</Value>
          <Value name="Check Box (58)">False</Value>
          <Value name="Check Box (70)">False</Value>
          <Value name="Check Box (77)">False</Value>
          <Value name="Drop Down (81)">upper</Value>
        </Configuration>
        <Annotation DisplayMode="0">
          <Name />
          <DefaultAnnotationText />
          <Left value="False" />
        </Annotation>
        <Dependencies>
          <Implicit />
        </Dependencies>
      </Properties>
      <EngineSettings Macro="Cleanse.yxmc" />
    </Node>
    <Node ToolID="3">
      <GuiSettings Plugin="AlteryxBasePluginsGui.BrowseV2.BrowseV2">
        <Position x="366" y="138" />
      </GuiSettings>
      <Properties>
        <Configuration>
          <TempFile>C:\Users\Krovvidi\AppData\Local\Temp\Engine_2600_6d4fb53d7a2e4a7c86598317a396376a_\Engine_1640_fe07784bdb6a4ea5a7700661b9f76447_.yxdb</TempFile>
          <TempFileDataProfiling />
          <Layout>
            <ViewMode>Single</ViewMode>
            <ViewSize value="100" />
            <View1>
              <DefaultTab>Profile</DefaultTab>
              <Hints>
                <Table />
              </Hints>
            </View1>
            <View2 />
          </Layout>
        </Configuration>
        <Annotation DisplayMode="0">
          <Name />
          <DefaultAnnotationText />
          <Left value="False" />
        </Annotation>
      </Properties>
      <EngineSettings EngineDll="AlteryxBasePluginsEngine.dll" EngineDllEntryPoint="AlteryxBrowseV2" />
    </Node>
  </Nodes>
  <Connections>
    <Connection>
      <Origin ToolID="1" Connection="Output" />
      <Destination ToolID="2" Connection="Input2" />
    </Connection>
    <Connection>
      <Origin ToolID="2" Connection="Output26" />
      <Destination ToolID="3" Connection="Input" />
    </Connection>
  </Connections>
  <Properties>
    <Memory default="True" />
    <GlobalRecordLimit value="0" />
    <TempFiles default="True" />
    <Annotation on="True" includeToolName="False" />
    <ConvErrorLimit value="10" />
    <ConvErrorLimit_Stop value="False" />
    <CancelOnError value="False" />
    <DisableBrowse value="False" />
    <EnablePerformanceProfiling value="False" />
    <DisableAllOutput value="False" />
    <ShowAllMacroMessages value="False" />
    <ShowConnectionStatusIsOn value="True" />
    <ShowConnectionStatusOnlyWhenRunning value="True" />
    <ZoomLevel value="0" />
    <LayoutType>Horizontal</LayoutType>
    <MetaInfo>
      <NameIsFileName value="True" />
      <Name>datacleansing</Name>
      <Description />
      <RootToolName />
      <ToolVersion />
      <ToolInDb value="False" />
      <CategoryName />
      <SearchTags />
      <Author />
      <Company />
      <Copyright />
      <DescriptionLink actual="" displayed="" />
      <Example>
        <Description />
        <File />
      </Example>
    </MetaInfo>
    <Events>
      <Enabled value="True" />
    </Events>
  </Properties>
</AlteryxDocument>

[r4upadhye]

can u share the workflow?