| Author |
Message |
Female
Newbie
Joined: 25 May 2016
Online Status: Offline
Posts: 2
|
  
Topic: return results based on mixed conditions
Posted: 27 Oct 2016 at 7:10am |
|
I'm sure this is simple but I just can't wrap my head around it.
Fields are as follows:
product# purchase year dept sub debt
I need only those with dept equal to hardware or software and sub debt equal to 0000 for hardware and 0001 for sub debt for the same years.
So....for example:
product# purchase year dept sub debt
111 2013 hardware 0000
111 2013 software 0000
111 2014 hardware 0000
111 2015 hardware 0000
111 2015 software 0001
111 2016 hardware 0000
I would ONLY see the results for 2015 in my query. I already have a formula set up to return duplicated years but I need it to drill further to only return the corresponding debt and sub debt.
Thanks in advance!
|
 IP Logged |
|
|
|
Female
Newbie
Joined: 25 May 2016
Online Status: Offline
Posts: 2
|
Posted: 28 Oct 2016 at 4:15am |
|
In case anyone is interested I found a solution. I joined the table to itself via aliases. Here's the example I found. Hope it helps the next person.
SELECT
t1.field1,
t2.field1,
t1.field2,
t2.field2,
t1.field3,
t2.field3
FROM
`tablename` t1
JOIN
`tablename` t2
ON
t1.field1 = t2.field1 and t1.field2 = t2.field2
WHERE
t1.field1 = 'search criteria1' and t1.field2 = 'search critera2'
AND
t2. = 'search criteria x' and t2.field2 = 'search critera y"
|
| IP Logged |
|
Sastry
Moderator
Joined: 16 Jul 2012
Online Status: Offline
Posts: 537
|
Posted: 02 Nov 2016 at 5:43am |
|
Hi,
In record selection use below formula: (Note : please replace the fields with exact field in your report)
Report-- Selection Formulas-- Record :
{Sheet1_.purchase} in ['Software','Hardware'] and
({Sheet1_.purchase} = 'Hardware' and {Sheet1_.dept} = '0000'
or
{Sheet1_.purchase} = 'Software' and {Sheet1_.dept} = '0001')
Report-- Selection Formulas-- Group :
count({Sheet1_.purchase},{Sheet1_.year}) = 2
Thanks,
Sastry
|
|
Thanks,
Sastry
|
| IP Logged |
|
|
|
