6 Replies Latest reply on Oct 17, 2018 3:07 PM by Zach Leber

    Tableau Logic help.

    syed abdul qayyum

      Hi all,

      the custom sql logic based on parameter is not working if I selects the month october or november or december. It is working fine from jan to september.

      I believe the problem with the logic -0 just before the month parameter.

      What is the other way around to correct the logic. can anybody please help

       

      Thank you

       

      SELECT *

      FROM ODM_SCD.IM_ActlFcstCmbnd_Fct_v   im

      WHERE  ((im.GblOpernsMtrcTypeCd = 'ACTL' and SubmtMoRlvncDt <= '<Parameters.Choose Year_Submit>-0<Parameters.Choose Month_Submit>-01')

      OR (im.GblOpernsMtrcTypeCd = 'FCST' and SubmtMoRlvncDt = '<Parameters.Choose Year_Submit>-0<Parameters.Choose Month_Submit>-01') )

       

      FROM ODM_SCD.IM_ActlFcstCmbnd_Fct_v   im

      WHERE  ((im.GblOpernsMtrcTypeCd = 'ACTL' and SubmtMoRlvncDt <= '2018-011-01')

      OR (im.GblOpernsMtrcTypeCd = 'FCST' and SubmtMoRlvncDt = '<Parameters.Choose Year_Submit>-0<Parameters.Choose Month_Submit>-01') )

       

       

        • 1. Re: Tableau Logic help.
          Ombir Rathee

          It seems like that data type of month parameter is integer. That is why you're adding zero infront of it. Follow below steps:

           

          1. Change data type of parameter to String and add 0 infront of all month values from 1 to 9 inside the parameter.

          2. Modify where clause in custom sql as below

           

          SubmtMoRlvncDt =<Parameters.Choose Year_Submit>+'-'+<Parameters.Choose Month_Submit>+'-01'

          1 of 1 people found this helpful
          • 2. Re: Tableau Logic help.
            Zach Leber

            You beat me to it Ombir, I'm deleting my identical post.

            1 of 1 people found this helpful
            • 4. Re: Tableau Logic help.
              syed abdul qayyum

              <Parameters.Choose Year_Submit> is set to float does it has to be string too.

              Please check the query below.

               

              SubmtMoRlvncDt =<Parameters.Choose Year_Submit>+'-'+<Parameters.Choose Month_Submit>+'-01' This was your answer

               

              Do we also need a '  just before the <Parameters.Choose Year_Submit>

               

               

              SELECT *

              FROM ODM_SCD.IM_ActlFcstCmbnd_Fct_v   im

              WHERE  ((im.GblOpernsMtrcTypeCd = 'ACTL' and SubmtMoRlvncDt <= '<Parameters.Choose Year_Submit>-0<Parameters.Choose Month_Submit>-01')

              OR (im.GblOpernsMtrcTypeCd = 'FCST' and SubmtMoRlvncDt = '<Parameters.Choose Year_Submit>-0<Parameters.Choose Month_Submit>-01') )

              • 5. Re: Tableau Logic help.
                syed abdul qayyum

                In the custom sql in the where we are trying to concatenate  two strings together

                is a string  2018

                Parameters.Choose Year_Submit is a string that is equal 2018

                Parameters.Choose Month_Submit is a string that is equal -06-01

                Examples we already tried that does not work

                 

                1)Select* from abc.inv where submit='<Parameters.Choose Year_Submit>' + '<Parameters.Choose Month_Submit>'

                 

                2)Select* from abc.inv where submit=<Parameters.Choose Year_Submit>>+ <Parameters.Choose Month_Submit>

                 

                3)Select* from abc.inv where submit='<Parameters.Choose Year_Submit>'  '<Parameters.Choose Month_Submit>'

                • 6. Re: Tableau Logic help.
                  Zach Leber

                  No quotes around the Tableau string parameters because they get inserted by Tableau with quotes. You only need to quote the string literals you are adding, e.g.

                   

                  <ParameterYearString>+'-'+<ParameterMonthString>+'-01'

                   

                  That's what Ombir wrote.