Using a macro as a function to call on column

Help
,
1

Using macro as a function

I’m trying to create a macro that is called on a column like a function which turns numbers to strings and adds commas to make the numbers more readable. In the future I wanted to add more logic that would add ‘k’ or ‘M’ after the number for thousands or millions, respectively.

I have the logic working in a Python script before even trying Jinja, which I’m new to, I’m sure I have syntax or maybe even the whole concept of using macros wrong.

Context:

There is a frontend dashboard formatted in such a way that it requires formatting in the raw before the data makes it to the dashboard GUI.

My current macro (logic incomplete)

 {% macro add_commas(input_val, round_prec) %}

   {{ log("Input Passed: " ~ input_val, True)}}
   {% set input_round =  input_val|round(round_prec) %}

   {% set string_input = input_round|string %}
 
   {% set str_decimal_split = string_input.split('.') %}
   
   {{ log("str decimal split: " ~ str_decimal_split, True) }}    
   
   {% set characters = str_decimal_split[0]|list() %}
   
   {{ log("characters: " ~ characters, True) }}   

   {% set output_list = [] %}

   {% for i in range(1 , (characters|length) + 1) %}
        {% set ind = i*-1 %}
        {% if i % 3 == 0 %}        
            
            {% do output_list.append(characters[ind]) %}
            {% do output_list.append(',') %}
            
        
        {%- else -%}
            
            {% do output_list.append(characters[ind]) %}

        {% endif %}
     {% endfor %} 
    {{ log("After Loop Output list: " ~ output_list, True) }}

    {% set output_list = output_list[::-1] %}
    {% set return_str = output_list|join('')|string %}

    {% if round_prec == 0 %}
        {{ return_str }}
    {% else %}
         {{return_str ~ '.' ~ str_decimal_split[1] }}
    {% endif %}
    
{% endmacro %}

Test model

I also tried hardcoding inside, ex. {{ add_commas(12345.236, 2) }}

with test_data as (
    select 12 as val
    union
    select 1234 as  val
    union
    select 123456.52 as val      
    )

    select {{ add_commas(a.val, 2) }} as valu
    from test_data a
2

I believe the problem is that jinja just renders SQL code, it does not have access to your column’s values

So Jinja does not know that the value is 12 or 123456.52 and it will not manipulate it

You can try finding some SQL functions from your DW that do what you want or using python models

1 Like