Hard-coding values based on a condition

[#1]

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!

[#2]

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.

Erica

[#3]

This is helpful!

What if i need to hardcode values where a field does not exist in Table_2?

For example:

Table 1 - 'Address' (Field contaings diferent text values)

Table 2 - field does not exist in the DB.  So I need to hard-code values to coming from table 2 to say 'Home'.

Thank you

[#4 · Accepted solution]

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:

Load

Field1 as [Field Name],

Field2,

Field3,

.....

'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'

Erica