Grouping/Sorting of Information

[MadeInHB]

I have been having some trouble figuring this out.  Below is an example:

Demo                                      Employee Number

Employee1                              123456

Spouse1                                  000000

Employee2                               654321

Child2                                      654321

Employee3                               000000

I am looking at trying to filter out the 000000 under the Employee Number, however keep the Employee with their families.  In this example, Employee 1 and their Spouse 1 along with Employee 3 would be filtered to one side (Employee1 stays with Spouse 1) and then Employee 2 and Child 2 go another route because both have the Employee Number listed.

Any help would be appreciated.

Thanks,

[jdunkerley79]

I would use a RegEx tool in parse mode to separate the Type and Id:

([[:alpha:]]+)(\d+)

This will give you two columns (Type and Id) which you can the use filters and then joins to work with.

Put together a quick demo of what I mean.

[michael_treadwell]

The solution that @jdunkerley79 gave will certainly work for the demo data that you provided but I worry that your actual data may be:

Name	Employee ID
Jack	123456
Jane	000000
John	654321
Sammy	654321
Jill	000000

This gives no real way to tell that a [Name] is tied as a spouse or child to an employee other than the order of the data. Is this true or am I overthinking the problem?

[MadeInHB]

Actually you are right.  It would be listed as names.  The order would be the same.  The Employee is first and then family is after until another employee is listed.

[michael_treadwell]

Is there any indiction in the data like another column that tells you whether or not someone is an employee? That way we know where each family unit begins and ends.

[MadeInHB]

Yes,  We have a code that is E, S, C  (E=Employee, S=Spouse, C=Child) 

I forgot one part as well. 

It would be like:

Name    Code     Employee Number     SSN

John     E           123456                      123456789

But dependnats sometimes have an SSN and sometimes they don't.  I want to filter out the entire family if any of the family has a blank SSN.

[michael_treadwell]

Alright, given that the families will be in order and one family ends when another employee is listed, this should solve your problem. Just keep in mind this module depends on that ordering.

The key is to use a Multi-Row tool to give a unique family id to each family unit

[MadeInHB]

So far this is working.  But it is only grouping in sets of two.  Is there a code that will group the record number the same until the next E shows up?

[michael_treadwell · Accepted solution]

Apologies, I made an error in my conditional statement in the Multi-Row tool. This should fix it.