CODEWITHSUNDEEP
google-bigquerymacrosjinja2dbt
trillion
trillion

Reputation: 1401

looping through columns passed to an existing macro in DBT

I have a macro that creates a boolean field with the values 0 and 1. The 0 and 1 are generated due to the fact that I am comparing values from two columns i.e s1, s2

s1 columns: x, y ,z s2 columns: x2, y2, z2

x column is compared with x2 column, similarly y column with y2 column and z with z2 column.

column

macro:

{% macro create_value(s1, s2) -%}
if(ifnull({{s1}} != {{s2}},true) is true,1,0) as {{ s1 | replace('xx','new') }}
{% endmacro %}

Calling the macro:

compute_value as (
select *,
    {{ create_value('xx_col1','xx_col2') }},
    {{ create_value('xx_col3','xx_col4') }},
    {{ create_value('xx_col5','xx_col6') }},
    {{ create_value('xx_col7','xx_col8') }},
    {{ create_value('xx_col9','xx_col10') }},
    ...
    {{ create_value('xx_col15','xxcol_16') }},
from
table
)

running the above macro will create the 8 new columns that are:

Once I have these 8 columns, I have to calculate the sum of the values Till this point I have everything. Currently, I am doing it manually by writing all the column names generated from the first macro and summing them up (see below):

select
*,
    new_col1 + new_col3 + new_col5 + new_col7 ....... + new_col15 AS new_value

from compute_value

Technically, I am getting my answers right, what I would like to automate is the calculation of the column new_value.

My first macro creates 8 columns and then I have to manually write the names of all 8 columns and sum them up. Is there a way that the sum of these columns can be automated as well i.e removing this part:

    new_col1 + new_col3 + new_col5 + new_col7 ....... + new_col5 AS new_value

currently, the complete code looks like this:

compute_value as (
select *,
    {{ create_value('xx_col1','xx_col2') }},
    {{ create_value('xx_col3','xx_col4') }},
    {{ create_value('xx_col5','xx_col6') }},
    {{ create_value('xx_col7','xx_col8') }},
    {{ create_value('xx_col9','xx_col10') }},
    ...
    {{ create_value('xx_col15','xxcol_16') }},
from
table
),

select 
*,
    new_col1 + new_col2 + new_col3 + new_col4 + new_col5 + new_col6 + new_col7 + new_col8 AS new_value

from compute_value

I need the 8 boolean values as well as the total sum of those values as the output

Upvotes: 0

Views: 1170

Answers (1)

tconbeer
tconbeer

Reputation: 5805

You could DRY this up a little by creating a jinja list, storing the names of the columns in that list, and looping over it twice. With only 8 columns, this might not save you much typing, and would arguably be harder to read, but it would look like this:

{% set cols = [("xxcol1", "col2"), ("xxcol3", "col4"), ("xxcol5", "col6"), ("xxcol7", "col8"), ...] %}
select
    {% for col1, col2 in cols %}
    {{ create_value(col1, col2) }},
    {% endfor %}
    {% for col1, _ in cols -%}
    {{ col1 | replace('xx','new_') }}{% if not loop.last %} +{% endif %}
    {%- endfor %} as new_value,
...

Compiles to:

select
    {{ create_value(xxcol1, col2) }},
    {{ create_value(xxcol3, col4) }},
    {{ create_value(xxcol5, col6) }},
    {{ create_value(xxcol7, col8) }},
    new_col1 + new_col3 + new_col5 + new_col7 as new_value,
...

If you RDBMS does not support lateral column aliases, you have to rework your macro to pull out the aliasing:

{% macro create_value(s1, s2) -%}
if(ifnull({{s1}} != {{s2}},true) is true,1,0)
{% endmacro %}

and then just call the macro in each loop:

{% set cols = [("xxcol1", "col2"), ("xxcol3", "col4"), ("xxcol5", "col6"), ("xxcol7", "col8"), ...] %}
select
    {% for col1, col2 in cols %}
    {{ create_value(col1, col2) }} as {{ col1 | replace('xx','new_') }},
    {% endfor %}
    {% for col1, col2 in cols -%}
    {{ create_value(col1, col2) }}{% if not loop.last %} +{% endif %}
    {%- endfor %} as new_value,
...

Upvotes: 1

Related Questions