Question

How to split a number with dots in a column

  • 13 August 2019
  • 6 replies
  • 609 views

Hi! 🙂


I have this column in my table which I can’t change the format.


17.298.3

17.46.1

17.4.7

17.298.12

etc…


Actually, this should be an ordinal number, that’s what I am looking for to do.


The 1st part (17) is useless.

After that, I need to use to 2nd and the 3rd number and apply them a formula in order to find out the ordinal number.


I.e.:

298.3 is (298-3)*15+20

46.1 is (46-3)*15+20

etc…


My biggest problem here is how to split the column in order to get 3 “new” columns.


Someone could help me, please?


Thanks a lot!!!

Guiche


6 replies

Userlevel 6
Badge +1

It begs for some regular expressions functions in Looker 😃


Is the first “useless” number always 2 digits?

Userlevel 1

You can try split function in your dimension, this works for snowflake DWH.



Blockquote select split(‘127.0.0.1’, ‘.’)[0];

select split(‘127.0.0.1’, ‘.’)[1];


I’d say yes

I’ve found a work around guys, do not spend more time in my case please xD

Thank you for your help!

Userlevel 7
Badge +1

We’d love to hear what you wound up doing!

Well, it’s not that exciting… sorry. We’ve built a quick new table with the translation of those numbers.

A simple JOIN translate everything for me. 🙂

Reply