Check for decimal points and load new column

[#1]

I have this myCode column which consist of numbers in the format of eg 1, 1.2, 1.2.3

As you can see the maximum format is 1.2.3. There is no 1.2.3.4 etc

If the format has no decimal point, eg(1 or 2 or 15 etc), I would like to load a column called NEWCOLUMN which has a value of 'L1'. If the format has 1 decimal point, eg(1.2 or 1.3 or 5.4 etc), I would like to load a column called NEWCOLUMN which has a value of 'L2'.

If the format has 2 decimal point, eg(1.2.3 or 1.3.2 or 5.4.5 etc), I would like to load a column called NEWCOLUMN which has a value of 'L3'.

So my expected output would be

myCode           NEWCOLUMN

1                               L1

1.1                             L2

1.4.7                          L3

1.5                              L2

8                                 L1

and so on.

What is the script to load this way? Im thinking something like

pseudocode

LOAD

IF(myCode has 2 decimal, 'L3' , IF(myCode has 1 decimal, 'L2', IF(myCode has 0 decimal, 'L1')) AS NEWCOLUMN

Thanks

[lironbaram]

hi

you can try something like this

load *,

       'L' & SubStringCount(myCode,'.')+1 AS NewColumn

[sdmech81]

check attachment..

Sachin

[sdmech81]

here is the excel