3 Replies Latest reply: Feb 11, 2013 5:01 PM by Erica Whalley RSS

    Hard-coding values based on a condition

      Hi -


      I am working with two tables that are being sourced from 2 different DB's.


      The requirement is to merge two tables into a single report. For most of the fields, I could define relationships using alias as part of the sql statement and now I need help with hard coding values.


      For example:


      Data Source Table 1                   Data Source Table 2       Expected Report Results

      'Type'                                           N/A                                  If value from Table 1 = Type --> display Type for tranactions coming from Table 2

      N/A                                             'Calendar '                         If values from Table 2  = Calendar--> Display Calendar for transactions coming from Table 1 


      What is the best way to aproach this in QV.


      Thank you!

        • Re: Hard-coding values based on a condition

          Hi Zvirina


          You can use "if" statements in the script. So nearly what you wrote there and you just need to nest them, eg:


          If( T1_Value = 'Type', [Trans2 Types field],

                    If( T1_Value = 'Calendar', [Trans2 Calendar field])



          Is this the sort of thing you are after? I am assuming you joined your tables together, if not, let me know.



          • Re: Hard-coding values based on a condition

            Hi Zvirina


            If the field does not exist in the DB, just add it as text and "call" it the same name as the field you need. Just the same as when you rename a field, use the "as" to rename it. EG when loading table 2:



            Field1 as [Field Name],




            'Home'     as Address,

            Field 10

            From ...Table 2...;


            This would Load all the information in field 1 but call it [Field Name], and will create a field adjacent to the table 2 data which just contains the word 'Home'