I need a flag that differentiate between number with decimal and without decimal

[vsap2000]

Hello,

I have a work order id which has Work Order # which has Sub-Work order # but there are cases where work order # doesn't have sub-work order #, I want to create a flag which identifies Work Order# and it has Sub-Work Order # and Work Order # doesn't have Sub-Work Order #.

For Eg

Work Ord Num

1

1.1

1.1.1

2

3

3.1

4

4.1

4.1.2

5

6

So I want a flag which will identify 1,1.1,1.1.1,3,3.1,4,4.1,4.1.2 as Work Ord # with Sub Ord# bucket and 2,5,6 in without Sub order#.

Thanks in advance.

-V

[stevejoyce]

table:
load
*
,if(SubStringCount(WorkOrdNum, '.'), 'Y', 'N') as Is_SubWorkOrd
,subfield(WorkOrdNum, '.', 1) as BaseWorkOrdNum

inline [
WorkOrdNum

1

1.1

1.1.1

2

3

3.1

4

4.1

4.1.2

5

6
]

;

//Get base work order numbers that have a sub order
workorder_with_suborder:
//left join (table)
load distinct
BaseWorkOrdNum
,BaseWorkOrdNum as exists_BaseWorkOrdNum_HasSubOrder
,'Y' as WorkOrder_Has_SubOrder
resident table
where Is_SubWorkOrd = 'Y';

//concatenate work orders that do NOT have sub order
concatenate(workorder_with_suborder)
//left join (table)
load distinct
BaseWorkOrdNum
,'N' as WorkOrder_Has_SubOrder
resident table
where not exists(exists_BaseWorkOrdNum_HasSubOrder, BaseWorkOrdNum);

//join back and drop
left join (table)
load
BaseWorkOrdNum
,WorkOrder_Has_SubOrder
resident workorder_with_suborder;

drop table workorder_with_suborder;

WorkOrder_Has_SubOrder is your flag field:  Y/N

[vsap2000]

Thanks for the reply, sorry, I should be more clear in my question can this be achieved using Set Analysis?

Best Regards,

-V