5 Replies Latest reply on Aug 23, 2016 9:52 AM by martin luccanera

    String of las 6 months for regex query

    martin luccanera

      Hi everybody. I'm in need of a way to translate last six months into a regex to query BigQuery.

       

      Example:

      If the user selects June/2016 i need to obtain

      (2016)(01|02|03|04|05|06)

       

      but if user selects January/2016 i need

      (2015)(|08|09|10|11|12)|(201601)

       

      Or something like that. The date will come from a simple date parameter and I plan to obtain the string from a calculated field.

       

      Do you know of any "easy" way to do this?

        • 1. Re: String of las 6 months for regex query
          Tom W

          You wouldn't use regex to do this.

          It would be easy enough to chain together some IF statements. Before you go down that rabbit hole, are you sure the second example is correct? I would expect it to look like (2015)(|08|09|10|11|12)|(2016)(01) based on the previous pattern. Or does it always do this if you're selecting the first month in a year?

          • 2. Re: String of las 6 months for regex query
            martin luccanera

            Regex are needed for the BigQuery part. Table names are named with patterns much like this: *clientId_yyyyMM* (where * represents other identifiers I don't need).

             

            I don't have the exact regex syntax so far (I need to learn more about it) but for instance (2016)(01|02|03) I believe is correct and matches all tables that contain 201601, 201602 and 201603.

             

            I'm trying to avoid a HUGE case/if if possible in tableau. I'm currently working on something like this (pseudo code):

             

            IF (INT(MONTH([date])) = 6)

            THEN STRING(YEAR([date])) + STRING("(01|02|03|04|05|06)")

             

            But this gets more complex when dealing with previous year and the IF-THEN gets really long (same with CASE statement).

             

            I would like to know if there is a different way to achieve this.

            • 3. Re: String of las 6 months for regex query
              Yuriy Fal

              Hi martin,

               

              Why not use brute force like this:

              '('

              + STR( DATEPART('year',  DATEADD('month',-5,[Date Parameter]) ) ) + RIGHT(STR('0')

              + STR( DATEPART('month', DATEADD('month',-5,[Date Parameter]) ) ), 2)

              + '|'

              + STR( DATEPART('year',  DATEADD('month',-4,[Date Parameter]) ) ) + RIGHT(STR('0')

              + STR( DATEPART('month', DATEADD('month',-4,[Date Parameter]) ) ), 2)

              + '|'

              + STR( DATEPART('year',  DATEADD('month',-3,[Date Parameter]) ) ) + RIGHT(STR('0')

              + STR( DATEPART('month', DATEADD('month',-3,[Date Parameter]) ) ), 2)

              + '|'

              + STR( DATEPART('year',  DATEADD('month',-2,[Date Parameter]) ) ) + RIGHT(STR('0')

              + STR( DATEPART('month', DATEADD('month',-2,[Date Parameter]) ) ), 2)

              + '|'

              + STR( DATEPART('year',  DATEADD('month',-1,[Date Parameter]) ) ) + RIGHT(STR('0')

              + STR( DATEPART('month', DATEADD('month',-1,[Date Parameter]) ) ), 2)

              + '|'

              + STR( DATEPART('year',  DATEADD('month', 0,[Date Parameter]) ) ) + RIGHT(STR('0')

              + STR( DATEPART('month', DATEADD('month', 0,[Date Parameter]) ) ), 2)

              + ')'

              Yours,

              Yuri

              1 of 1 people found this helpful
              • 4. Re: String of las 6 months for regex query
                martin luccanera

                Sorry for the delay in answering (I faced a lot of issues with something not specific to this question and took me a lot of time)

                 

                I ended up doing it like this

                 

                CASE INT(DATEPART('month',[

                SelectDate]))
                WHEN 1 THEN "("+"("+"("+STR((INT(YEAR([SelectDate]))-1)) + ")" + STR("(08|09|10|11|12)")+")"+"|"+"("+"("+STR(YEAR([SelectDate]))+")"+"(01)"+")"+")"
                WHEN 2 THEN "("+"("+"("+STR((INT(YEAR([SelectDate]))-1)) + ")" + STR("(09|10|11|12)")+")"+"|"+"("+"("+STR(YEAR([SelectDate]))+")"+"(01|02)"+")"+")"
                WHEN 3 THEN "("+"("+"("+STR((INT(YEAR([SelectDate]))-1)) + ")" + STR("(10|11|12)")+")"+"|"+"("+"("+STR(YEAR([SelectDate]))+")"+"(01|02|03)"+")"+")"
                WHEN 4 THEN "("+"("+"("+STR((INT(YEAR([SelectDate]))-1)) + ")" + STR("(11|12)")+")"+"|"+"("+"("+STR(YEAR([SelectDate]))+")"+"(01|02|03|04)"+")"+")"
                WHEN 5 THEN "("+"("+"("+STR((INT(YEAR([SelectDate]))-1)) + ")" + STR("(12)")+")"+"|"+"("+"("+STR(YEAR([SelectDate]))+")"+"(01|02|03|04|05)"+")"+")"
                WHEN 6 THEN "("+STR(YEAR([SelectDate]))+")"+STR("(01|02|03|04|05|06)")
                WHEN 7 THEN "("+STR(YEAR([SelectDate]))+")"+STR("(02|03|04|05|06|07)")
                WHEN 8 THEN "("+STR(YEAR([SelectDate]))+")"+STR("(03|04|05|06|07|08)")
                WHEN 9 THEN "("+STR(YEAR([SelectDate]))+")"+STR("(04|05|06|07|08|09)")
                WHEN 10 THEN "("+STR(YEAR([SelectDate]))+")"+STR("(05|06|07|08|09|10)")
                WHEN 11 THEN "("+STR(YEAR([SelectDate]))+")"+STR("(06|07|08|09|10|11)")
                ELSE "("+STR(YEAR([SelectDate]))+")"+STR("(07|08|09|10|11|12)")
                END

                 

                 

                But in the end it didn't work out because bigquery doesn't like how tableau wraps parameters. So I'm gonna start a new topic for people to help me with those specific issues between tableau and BQ.

                 

                Thanks for your replies