7 Replies Latest reply on Jan 30, 2019 7:59 PM by Mahfooj Khan

    SQL Statement not working with text file

    Bhavy  Dikshit

      Hi,

       

      I have a Custom SQL query that I am using on a SQL table and it runs perfectly fine. However, when I use a text file (pipe-delimeted) containing the same data instead of the SQL connection as my data source, and try to use the same Custom SQL query, it throws an error. Below is the query that I am using which contains several filters as parameters

       

      select *, case when <Parameters.Time Frame for Cost>='90' and <Parameters.Diagnosis Code to Include>='Single' then [Amt_90]

      when <Parameters.Time Frame for Cost>='120' and <Parameters.Diagnosis Code to Include>='Single' then [Amt_120]

      when <Parameters.Time Frame for Cost>='150' and <Parameters.Diagnosis Code to Include>='Single' then [Amt_150]

      when <Parameters.Time Frame for Cost>='180' and <Parameters.Diagnosis Code to Include>='Single' then [Amt_180]

      when <Parameters.Time Frame for Cost>='90' and <Parameters.Diagnosis Code to Include>='All' then [Amt_All_90]

      when <Parameters.Time Frame for Cost>='120' and <Parameters.Diagnosis Code to Include>='All' then [Amt_All_120]

      when <Parameters.Time Frame for Cost>='150' and <Parameters.Diagnosis Code to Include>='All' then [Amt_All_150]

      when <Parameters.Time Frame for Cost>='180' and <Parameters.Diagnosis Code to Include>='All' then [Amt_All_180] end as Amount,

      case when <Parameters.Cool-Off Period>='90' then DATEADD(dd,-90,Svc_Date)

      when <Parameters.Cool-Off Period>='120' then DATEADD(dd,-120,Svc_Date)

      when <Parameters.Cool-Off Period>='150' then DATEADD(dd,-150,Svc_Date)

      when <Parameters.Cool-Off Period>='180' then DATEADD(dd,-180,Svc_Date)

      when <Parameters.Cool-Off Period>='1 year' then DATEADD(dd,-365,Svc_Date)

      when <Parameters.Cool-Off Period>='2 years' then DATEADD(dd,-730,Svc_Date) end as Svc_Date_Sub

      from [text_file#txt]

       

      Also, I am able to apply a simple where filter (Custom SQL) on this text file but the query above does not work.

       

      Thanks!

        • 1. Re: SQL Statement not working with text file
          Mahfooj Khan

          Can you share some sample mock up data in text file so that we can validate the query?

          • 2. Re: SQL Statement not working with text file
            Bhavy  Dikshit

            Attached some sample mock up data from the text file.

            At the same time, my concern is not the logic but simply getting the case statement to work. Even a simple SQL query as below is giving me an error.

             

            SELECT case when <Parameters.Time Frame for Cost>='90'

            then 'abc' as dummy

            FROM [text_file#txt]

            • 3. Re: SQL Statement not working with text file
              Mahfooj Khan

              Hi,

               

              Find my approach in attached workbook. Let us know if this is what you wanted?

              Play with the parameters to check the expected values.

               

              Mahfooj

              • 4. Re: SQL Statement not working with text file
                Bhavy  Dikshit

                Thank you for your response!
                So actually I am using sub queries to do transformations on the data on multiple layers and then finally create the visualizations using the output of these queries as the data source. The query that I sent earlier was a part of my complete query, sorry for not specifying everything in the first go. Below is the complete query.

                I understand now how I will be able to skip multiple case statements as mentioned in your solution (innermost part of the query). But I am still unable to do rest of the part - taking lag etc.

                 

                select *, case when LagDate>=Svc_Date_Sub then 0 else 1 end as Indicator

                from (

                select *,

                LAG(Svc_date) over (partition by dw_member_id, HighLvlDiag

                order by Svc_Date) as LagDate

                from (select * from (

                select *, case when <Parameters.Time Frame for Cost>='90' and <Parameters.Diagnosis Code to Include>='Single' then [Amt_90]

                when <Parameters.Time Frame for Cost>='120' and <Parameters.Diagnosis Code to Include>='Single' then [Amt_120]

                when <Parameters.Time Frame for Cost>='150' and <Parameters.Diagnosis Code to Include>='Single' then [Amt_150]

                when <Parameters.Time Frame for Cost>='180' and <Parameters.Diagnosis Code to Include>='Single' then [Amt_180]

                when <Parameters.Time Frame for Cost>='90' and <Parameters.Diagnosis Code to Include>='All' then [Amt_All_90]

                when <Parameters.Time Frame for Cost>='120' and <Parameters.Diagnosis Code to Include>='All' then [Amt_All_120]

                when <Parameters.Time Frame for Cost>='150' and <Parameters.Diagnosis Code to Include>='All' then [Amt_All_150]

                when <Parameters.Time Frame for Cost>='180' and <Parameters.Diagnosis Code to Include>='All' then [Amt_All_180] end as Amount,

                case when <Parameters.Cool-Off Period>='90' then DATEADD(dd,-90,Svc_Date)

                when <Parameters.Cool-Off Period>='120' then DATEADD(dd,-120,Svc_Date)

                when <Parameters.Cool-Off Period>='150' then DATEADD(dd,-150,Svc_Date)

                when <Parameters.Cool-Off Period>='180' then DATEADD(dd,-180,Svc_Date)

                when <Parameters.Cool-Off Period>='1 year' then DATEADD(dd,-365,Svc_Date)

                when <Parameters.Cool-Off Period>='2 years' then DATEADD(dd,-730,Svc_Date) end as Svc_Date_Sub

                from TableauDataFile_GR_processed_2)

                as T

                where Amount>=<Parameters.Threshold Cost>) as A) as B

                 

                Thank you!

                • 5. Re: SQL Statement not working with text file
                  Mahfooj Khan

                  Hi,

                   

                  Sorry to say its long time I haven't worked on SQL functions so couldn't translate what exactly your LAG() is doing. If you can explain little bit in plain English then I can try to write a calculated field to get the LAG Date.  However what I've understood is you're trying to get the minimum date from SVC Date member ID and High Lvl field. Here is my output. Take a look and let me know if this work.

                  Using FIXED{} LOD so have to extract the source. Workbook v10.5

                   

                  Mahfooj

                  • 6. Re: SQL Statement not working with text file
                    Bhavy  Dikshit

                    Thanks again for helping out !

                    I am trying to get just the previous date in Lag_date corresponding to a date in a record for the same member and diagnosis

                    Just in the example given above, let's say for a member 2 and diagnosis 48, there are three dates then:

                    Member Diagnosis    Date          Lag_date

                    2             48             12/9/2015   NULL

                    2             48              1/1/2016    12/9/2015

                    2             48             1/1/2017     1/1/2016

                     

                    Any help on how to calculate the lag date , either using custom sql query with text file as source, or within tableau would be greatly appreciated

                    • 7. Re: SQL Statement not working with text file
                      Mahfooj Khan

                      Hi,

                       

                      Thanks for the explaining the logic,

                       

                      I've changed the logic of LAG date and here my output take a look and let me know if this ok

                      LAG Date: LOOKUP(ATTR([Svc Date]),-1)

                      Workbook attached for your reference.

                       

                      Mahfooj