Skip to main content
Solved

Remove empty fields

arthy
Contributor
Forum|alt.badge.img+8
  • arthyContributor
  • Contributor

Is there a way to extract only the non-empty fields of an oracle table?

Let's say I have a table like this one,

    col_a    col_b    col_c    col_d   col_e
    NULL     NULL     NULL     NULL    5
    NULL     NULL     NULL     8       6
    0        NULL     NULL     NULL    3
    NULL     NULL     NULL     0       1

and the subtable that I would like to extract should be

    col_a    col_d   col_e
    NULL     NULL    5
    NULL     8       6
    0        NULL    3
    NULL     0       1

Thanks

Best answer by xiaomengatsafe

Another option is to use the AttributeExploder to turn each attribute name and value pair into individual features, then filter out only ones where value is not empty. And create a schema feature using the filtered result. In a Dynamic Writer, Schema definition can come from Schema features. This will ensure writer don't write out columns without any value.

 

I attached a sample workspace for reference. the source shapefile has one column "COUSUBNS" where all values are empty, on the output, that column is not written.

 

Hope this helps.

 

Also for more details on working with AttributeExploder result, you can reference the beginning part of this tutorial: https://knowledge.safe.com/articles/31701/transpose-a-table-using-fme.html

 

And on Dynamic writing where schema is defined by Schema Feature, you can read more here: https://knowledge.safe.com/articles/1051/index.html

 

remove-columns-with-only-empty-data.fmwt

View original
Did this help you find an answer to your question?

10 replies

erik_jan
Contributor
Forum|alt.badge.img+17
  • erik_janContributor
  • Contributor
  • January 9, 2017

After reading the data the NullAttributeMapper transformer (If Null set to Missing) can remove all Null attributes.

itay
Supporter
Forum|alt.badge.img+17
  • itaySupporter
  • Supporter
  • January 9, 2017

Have you tried a SQL statement on the database to read non-null columns?

select column_name
from user_tab_columns
where table_name='Table_name' and num_nulls=0;
arthy
Contributor
Forum|alt.badge.img+8
  • arthyContributor
  • Author
  • Contributor
  • January 9, 2017
erik_jan wrote:

After reading the data the NullAttributeMapper transformer (If Null set to Missing) can remove all Null attributes.

@erik_jan, In the example above, the input table has 5 columns while the output table has 3 columns.

 

The NullAttributeMapper won't give this result.

 

erik_jan
Contributor
Forum|alt.badge.img+17
  • erik_janContributor
  • Contributor
  • January 9, 2017
arthy wrote:
@erik_jan, In the example above, the input table has 5 columns while the output table has 3 columns.

 

The NullAttributeMapper won't give this result.

 

I see what you mean:

 

In that case I can only think of using the StatisticsCalculator (max value) and a Tester (If max value > 0). On Failed an AttributeRemover.

 

Not really straight forward but this could work.

 

 

xiaomengatsafe
Safer
Forum|alt.badge.img+3
  • xiaomengatsafeSafer
  • Safer
  • Best Answer
  • January 31, 2017

Another option is to use the AttributeExploder to turn each attribute name and value pair into individual features, then filter out only ones where value is not empty. And create a schema feature using the filtered result. In a Dynamic Writer, Schema definition can come from Schema features. This will ensure writer don't write out columns without any value.

 

I attached a sample workspace for reference. the source shapefile has one column "COUSUBNS" where all values are empty, on the output, that column is not written.

 

Hope this helps.

 

Also for more details on working with AttributeExploder result, you can reference the beginning part of this tutorial: https://knowledge.safe.com/articles/31701/transpose-a-table-using-fme.html

 

And on Dynamic writing where schema is defined by Schema Feature, you can read more here: https://knowledge.safe.com/articles/1051/index.html

 

remove-columns-with-only-empty-data.fmwt

jdh
Contributor
Forum|alt.badge.img+28
  • jdhContributor
  • Contributor
  • January 31, 2017
xiaomengatsafe wrote:

Another option is to use the AttributeExploder to turn each attribute name and value pair into individual features, then filter out only ones where value is not empty. And create a schema feature using the filtered result. In a Dynamic Writer, Schema definition can come from Schema features. This will ensure writer don't write out columns without any value.

 

I attached a sample workspace for reference. the source shapefile has one column "COUSUBNS" where all values are empty, on the output, that column is not written.

 

Hope this helps.

 

Also for more details on working with AttributeExploder result, you can reference the beginning part of this tutorial: https://knowledge.safe.com/articles/31701/transpose-a-table-using-fme.html

 

And on Dynamic writing where schema is defined by Schema Feature, you can read more here: https://knowledge.safe.com/articles/1051/index.html

 

remove-columns-with-only-empty-data.fmwt

I've done the equivalent to this using python to filter the attributes and create the schema feature.

 

 

trevorm
Contributor
Forum|alt.badge.img+3
  • trevormContributor
  • Contributor
  • August 17, 2022
xiaomengatsafe wrote:

Another option is to use the AttributeExploder to turn each attribute name and value pair into individual features, then filter out only ones where value is not empty. And create a schema feature using the filtered result. In a Dynamic Writer, Schema definition can come from Schema features. This will ensure writer don't write out columns without any value.

 

I attached a sample workspace for reference. the source shapefile has one column "COUSUBNS" where all values are empty, on the output, that column is not written.

 

Hope this helps.

 

Also for more details on working with AttributeExploder result, you can reference the beginning part of this tutorial: https://knowledge.safe.com/articles/31701/transpose-a-table-using-fme.html

 

And on Dynamic writing where schema is defined by Schema Feature, you can read more here: https://knowledge.safe.com/articles/1051/index.html

 

remove-columns-with-only-empty-data.fmwt

Hello I'm trying to use the Remove-columns-with-empty-data work space and Cosbuns appears to still show up in my FeatureMerger (End). The solution on my data works perfect on the top pathway and removes all my "all null columns" , but the merger appears to be bringing them all back in.

JennaKAtSafe
Safer
Forum|alt.badge.img+6
trevorm wrote:

Hello I'm trying to use the Remove-columns-with-empty-data work space and Cosbuns appears to still show up in my FeatureMerger (End). The solution on my data works perfect on the top pathway and removes all my "all null columns" , but the merger appears to be bringing them all back in.

Hi @trevorm​ , would you be able to provide screenshots or a sample of your workspace? I would also suggest starting a new thread with your question as this thread is fairly outdated

trevorm
Contributor
Forum|alt.badge.img+3
  • trevormContributor
  • Contributor
  • August 22, 2022
trevorm wrote:

Hello I'm trying to use the Remove-columns-with-empty-data work space and Cosbuns appears to still show up in my FeatureMerger (End). The solution on my data works perfect on the top pathway and removes all my "all null columns" , but the merger appears to be bringing them all back in.

Hi,

I can start a new thread, but how do I reference this threat as I'm trying to reproduce remove-columns-with-only-empty-data.fmwt

If the content on the forums are out of date or no longer working with the current version is there a way to remove them from my search results? I want to remove columns from my .csv that have all "null" values.

 

I have taken this work space and ran it <except the writer>

 

I'll start a new workspace with your help, as this is a great way to ensure my .csv gets cleaned without missing some data at the bottom.

evieatsafe
Safer
  • evieatsafeSafer
  • Safer
  • August 23, 2022
trevorm wrote:

Hello I'm trying to use the Remove-columns-with-empty-data work space and Cosbuns appears to still show up in my FeatureMerger (End). The solution on my data works perfect on the top pathway and removes all my "all null columns" , but the merger appears to be bringing them all back in.

Hi @trevorm​ to answer one of your questions: we are looking to improve the search function on the community to view the relevant version that applies to you; however, we are not quite there yet. Keep a look out for that in the future!

 

You can also start a new question with links to the resources that are relevant such as this question, articles, and even attach data and workspaces.

 

To answer your question about your column removal workspace question, and I believe that that workspace should work. Unless you have an issue that you could provide an example workspace for, I'm not sure I can fully answer your question. Look forward to a response!

Happy to help - Evie

Reply


Helpful Members This Week

Community Stats

31,135
Posts
118,046
Replies
38,777
Members

Latest FME

Terms & ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.