Data Prep extract for all nulls

Steve Caiels_21881 said:

Hi Janet,

I will pass this enhancement request to our developers. In the meantime, there is a workaround by using a negative Join . . .

Here is an initial data set similar to yours.

An extraction to "EXCLUDE all rows where ALL key columns contain : null" gives you the opposite of the data set you want.

 I think a NEGATIVE LEFT JOIN will get the data set you need.

Regards,
Steve.

------------------------------
Steve Caiels
Professional Services
Altair
------------------------------
-------------------------------------------
Original Message:
Sent: 09-29-2020 02:42 PM
From: Janet Grimsley
Subject: Data Prep extract for all nulls

Maybe this is an enhancement request?  I have 24 columns of data that I would like to extract the rows based on all columns being null for that record.  Tried using Extract Rows, but finally realized I can't use this feature to get the result I am looking for.  If there was an option to "INCLUDE all rows where ALL key columns contain:" I would be set.  Otherwise, I have to work with more logic over X number of columns which is a pain. 
In the screen shot I would want to result to give me Port# 109,111,114, 117, etc.
 

------------------------------
Janet Grimsley
VP, Business Intelligence
The Fauquier Bank
Warrenton VA
5403490215
------------------------------

Thanks Steve.  Worked perfectly.  I forget you sometimes need to think in the reverse.  

Janet

------------------------------
Janet Grimsley
VP, Business Intelligence
The Fauquier Bank
Warrenton VA
5403490215
------------------------------
-------------------------------------------
Original Message:
Sent: 09-30-2020 05:03 AM
From: Steve Caiels
Subject: Data Prep extract for all nulls

Hi Janet,

I will pass this enhancement request to our developers. In the meantime, there is a workaround by using a negative Join . . .

Here is an initial data set similar to yours.

An extraction to "EXCLUDE all rows where ALL key columns contain : null" gives you the opposite of the data set you want.

 I think a NEGATIVE LEFT JOIN will get the data set you need.

Regards,
Steve.

------------------------------
Steve Caiels
Professional Services
Altair
------------------------------

Original Message:
Sent: 09-29-2020 02:42 PM
From: Janet Grimsley
Subject: Data Prep extract for all nulls

Maybe this is an enhancement request?  I have 24 columns of data that I would like to extract the rows based on all columns being null for that record.  Tried using Extract Rows, but finally realized I can't use this feature to get the result I am looking for.  If there was an option to "INCLUDE all rows where ALL key columns contain:" I would be set.  Otherwise, I have to work with more logic over X number of columns which is a pain. 
In the screen shot I would want to result to give me Port# 109,111,114, 117, etc.
 

------------------------------
Janet Grimsley
VP, Business Intelligence
The Fauquier Bank
Warrenton VA
5403490215
------------------------------

Mahmoud said:

Hi Janet,
Create a calculated field and use the IF and IsNull functions.  For example the following expression checks only 4 fields (you can expand it for additional fields), and if all 4 fields are null, then assigns a value of 0 to a new field, otherwise a 1:
if(isnull(col1) .and. isnull(col2) .and. isnull(col3) .and. isnull(col4), 0, 1)

Create a filter base one the new field, which contains a value of 1.

Regards
Mo


------------------------------
Mahmoud Abdolrahim
Senior Implementation & Integration Engineer
Altair Engineering
------------------------------
-------------------------------------------
Original Message:
Sent: 09-29-2020 02:42 PM
From: Janet Grimsley
Subject: Data Prep extract for all nulls

Maybe this is an enhancement request?  I have 24 columns of data that I would like to extract the rows based on all columns being null for that record.  Tried using Extract Rows, but finally realized I can't use this feature to get the result I am looking for.  If there was an option to "INCLUDE all rows where ALL key columns contain:" I would be set.  Otherwise, I have to work with more logic over X number of columns which is a pain. 
In the screen shot I would want to result to give me Port# 109,111,114, 117, etc.
 

------------------------------
Janet Grimsley
VP, Business Intelligence
The Fauquier Bank
Warrenton VA
5403490215
------------------------------

Thanks Mo.  I did this for 24 columns, which wasn't too bad and it worked.  Then Steve suggested a negative join logic which worked as well.  The older I get the more tired my brain gets, so tell the developers I need "Extracted Rows" to work for all nulls.  LOL!!

------------------------------
Janet Grimsley
VP, Business Intelligence
The Fauquier Bank
Warrenton VA
5403490215
------------------------------
-------------------------------------------
Original Message:
Sent: 09-30-2020 01:29 PM
From: Mahmoud Abdolrahim
Subject: Data Prep extract for all nulls

Hi Janet,
Create a calculated field and use the IF and IsNull functions.  For example the following expression checks only 4 fields (you can expand it for additional fields), and if all 4 fields are null, then assigns a value of 0 to a new field, otherwise a 1:
if(isnull(col1) .and. isnull(col2) .and. isnull(col3) .and. isnull(col4), 0, 1)

Create a filter base one the new field, which contains a value of 1.

Regards
Mo


------------------------------
Mahmoud Abdolrahim
Senior Implementation & Integration Engineer
Altair Engineering
------------------------------

Original Message:
Sent: 09-29-2020 02:42 PM
From: Janet Grimsley
Subject: Data Prep extract for all nulls

Maybe this is an enhancement request?  I have 24 columns of data that I would like to extract the rows based on all columns being null for that record.  Tried using Extract Rows, but finally realized I can't use this feature to get the result I am looking for.  If there was an option to "INCLUDE all rows where ALL key columns contain:" I would be set.  Otherwise, I have to work with more logic over X number of columns which is a pain. 
In the screen shot I would want to result to give me Port# 109,111,114, 117, etc.
 

------------------------------
Janet Grimsley
VP, Business Intelligence
The Fauquier Bank
Warrenton VA
5403490215
------------------------------

Steve Caiels_21881 said:

Hi Janet,

Late last night, an alternative method came to mind. I think it's easier.  I know I need to get out more, but it's not so easy at the moment!

Taking the table below as an example . . .

• Click on the first column that you want to check, then shift click on the last column. This should highlight all [Number n] columns, in this case.
• Right click any column and select Merge-Merge Selected Columns.
• Remove the space from the delimiter. This will make the final filter insensitive to the number of columns you combine when you re-use the workspace. 
• Move the new column to a convenient place and you should have a single column that can be filtered for the blanks using the 'Multiple Selection' on the bank entry.

Regards,
Steve.

------------------------------
Steve Caiels
Professional Services
Altair
------------------------------
-------------------------------------------
Original Message:
Sent: 09-30-2020 01:46 PM
From: Janet Grimsley
Subject: Data Prep extract for all nulls

Thanks Mo.  I did this for 24 columns, which wasn't too bad and it worked.  Then Steve suggested a negative join logic which worked as well.  The older I get the more tired my brain gets, so tell the developers I need "Extracted Rows" to work for all nulls.  LOL!!

------------------------------
Janet Grimsley
VP, Business Intelligence
The Fauquier Bank
Warrenton VA
5403490215
------------------------------

Original Message:
Sent: 09-30-2020 01:29 PM
From: Mahmoud Abdolrahim
Subject: Data Prep extract for all nulls

Hi Janet,
Create a calculated field and use the IF and IsNull functions.  For example the following expression checks only 4 fields (you can expand it for additional fields), and if all 4 fields are null, then assigns a value of 0 to a new field, otherwise a 1:
if(isnull(col1) .and. isnull(col2) .and. isnull(col3) .and. isnull(col4), 0, 1)

Create a filter base one the new field, which contains a value of 1.

Regards
Mo


------------------------------
Mahmoud Abdolrahim
Senior Implementation & Integration Engineer
Altair Engineering

Original Message:
Sent: 09-29-2020 02:42 PM
From: Janet Grimsley
Subject: Data Prep extract for all nulls

Maybe this is an enhancement request?  I have 24 columns of data that I would like to extract the rows based on all columns being null for that record.  Tried using Extract Rows, but finally realized I can't use this feature to get the result I am looking for.  If there was an option to "INCLUDE all rows where ALL key columns contain:" I would be set.  Otherwise, I have to work with more logic over X number of columns which is a pain. 
In the screen shot I would want to result to give me Port# 109,111,114, 117, etc.
 

------------------------------
Janet Grimsley
VP, Business Intelligence
The Fauquier Bank
Warrenton VA
5403490215
------------------------------

Genius!  Each one of you are magical.   I am on version 15.3 still as I have to wait for Automator to catch up on versioning.  With that said, I had to go through the "Quick Merge" to find the delimiter box.  It worked exactly like you said.

We all need to get out more!  But, it is great to be in such great "virtual" company with my fellow Data Geeks.

------------------------------
Janet Grimsley
VP, Business Intelligence
The Fauquier Bank
Warrenton VA
5403490215
------------------------------
-------------------------------------------
Original Message:
Sent: 10-01-2020 04:46 AM
From: Steve Caiels
Subject: Data Prep extract for all nulls

Hi Janet,

Late last night, an alternative method came to mind. I think it's easier.  I know I need to get out more, but it's not so easy at the moment!

Taking the table below as an example . . .

• Click on the first column that you want to check, then shift click on the last column. This should highlight all [Number n] columns, in this case.
• Right click any column and select Merge-Merge Selected Columns.
• Remove the space from the delimiter. This will make the final filter insensitive to the number of columns you combine when you re-use the workspace. 
• Move the new column to a convenient place and you should have a single column that can be filtered for the blanks using the 'Multiple Selection' on the bank entry.

Regards,
Steve.

------------------------------
Steve Caiels
Professional Services
Altair
------------------------------

Original Message:
Sent: 09-30-2020 01:46 PM
From: Janet Grimsley
Subject: Data Prep extract for all nulls

Thanks Mo.  I did this for 24 columns, which wasn't too bad and it worked.  Then Steve suggested a negative join logic which worked as well.  The older I get the more tired my brain gets, so tell the developers I need "Extracted Rows" to work for all nulls.  LOL!!

------------------------------
Janet Grimsley
VP, Business Intelligence
The Fauquier Bank
Warrenton VA
5403490215

Original Message:
Sent: 09-30-2020 01:29 PM
From: Mahmoud Abdolrahim
Subject: Data Prep extract for all nulls

Hi Janet,
Create a calculated field and use the IF and IsNull functions.  For example the following expression checks only 4 fields (you can expand it for additional fields), and if all 4 fields are null, then assigns a value of 0 to a new field, otherwise a 1:
if(isnull(col1) .and. isnull(col2) .and. isnull(col3) .and. isnull(col4), 0, 1)

Create a filter base one the new field, which contains a value of 1.

Regards
Mo


------------------------------
Mahmoud Abdolrahim
Senior Implementation & Integration Engineer
Altair Engineering

Original Message:
Sent: 09-29-2020 02:42 PM
From: Janet Grimsley
Subject: Data Prep extract for all nulls

Maybe this is an enhancement request?  I have 24 columns of data that I would like to extract the rows based on all columns being null for that record.  Tried using Extract Rows, but finally realized I can't use this feature to get the result I am looking for.  If there was an option to "INCLUDE all rows where ALL key columns contain:" I would be set.  Otherwise, I have to work with more logic over X number of columns which is a pain. 
In the screen shot I would want to result to give me Port# 109,111,114, 117, etc.
 

------------------------------
Janet Grimsley
VP, Business Intelligence
The Fauquier Bank
Warrenton VA
5403490215
------------------------------