Looker Connect Training

Help Center

Documentation

Community

Cloud Certifications

Question

How to split a number with dots in a column

  • 13 August 2019
  • 6 replies
  • 1248 views
guiche

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

Dawid
Userlevel 7
Badge +1

It begs for some regular expressions functions in Looker 😃


Is the first “useless” number always 2 digits?

Dawid
Krishna_Shukla
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];


guiche

I’d say yes

guiche

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

Thank you for your help!

izzy
Userlevel 7
Badge +1

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

guiche

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


Terms of Service