Check for decimal points and load new column

Thu, 02 Feb 2017 07:41:27 GMT

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

Re: Check for decimal points and load new column

lironbaram — Thu, 02 Feb 2017 07:55:45 GMT

you can try something like this

load *,

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

Re: Check for decimal points and load new column

sdmech81 — Thu, 02 Feb 2017 08:10:16 GMT

check attachment..

Sachin

Re: Check for decimal points and load new column

sdmech81 — Thu, 02 Feb 2017 08:11:08 GMT

here is the excel

topic Re: Check for decimal points and load new column in QlikView

Check for decimal points and load new column

Re: Check for decimal points and load new column

Re: Check for decimal points and load new column

Re: Check for decimal points and load new column