Hi, I am new here and this is my first post, can someone please guide me here. Below is my sample data set Request IDFlag 1Flag 2Flag 3A100A010A100B100B001B010B000B010C001C010C000D010D000D100D001 I need to extract distinct Request ID's that have all three flags has 1 in any row into a new column. In the above table the final output column would identify 'B' and 'D' Request ID's as they have 1 in columns Flag 1, Flag 2, Flag 3.

Multi row tool or which tool to use?

Hi, I am new here and this is my first post, can someone please guide me here. Below is my sample data set

Request ID	Flag 1	Flag 2	Flag 3
A	1	0	0
A	0	1	0
A	1	0	0
B	1	0	0
B	0	0	1
B	0	1	0
B	0	0	0
B	0	1	0
C	0	0	1
C	0	1	0
C	0	0	0
D	0	1	0
D	0	0	0
D	1	0	0
D	0	0	1

I need to extract distinct Request ID's that have all three flags has 1 in any row into a new column. In the above table the final output column would identify 'B' and 'D' Request ID's as they have 1 in columns Flag 1, Flag 2, Flag 3.

Developer Tools

Accepted answers

atcodedog05

Hi @Rag1

Here is how you can do it.

Workflow:

1. Using summarize tool I am finding of max of flag for each record id. This way is a flag has atleast a row as 1it will be taken in max.

2. Using filter keep only record ids where max_flag1+max_flag2+max_flag3 (i,e all flag atleast one 1).

3. Using join tool joining on record id. This way only record ids with all flags present in outputted.

Hope this helps : )

all flags.yxmd

All comments

cmcclellan

I'd do this ...

- Summarize tool .... group by RequestID, sum Flag1, Flag2, Flag3

- Filter tool ... Flag1 > 0 and Flag2 > 0 and Flag3 > 0

messi007

@Rag1,

Please see below :

attached the worklfow,

Regards,

Filter_20210831.yxmd

atcodedog05

Hi @Rag1

Here is how you can do it.

Workflow:

1. Using summarize tool I am finding of max of flag for each record id. This way is a flag has atleast a row as 1it will be taken in max.

2. Using filter keep only record ids where max_flag1+max_flag2+max_flag3 (i,e all flag atleast one 1).

3. Using join tool joining on record id. This way only record ids with all flags present in outputted.

Hope this helps : )

all flags.yxmd

Rag1

Thanks everyone, all solutions worked!

atcodedog05

Happy to help : ) @Rag1

Cheers and have a nice day!

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

RithiS 280