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,

Transformation

Preparation

Accepted answers

michael_treadwell

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

family-ssn.yxmd

All comments

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.

RegExParts.yxmd

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.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703