Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello!
I have the following tables in my data model:
ENERGY:
ACCOUNT_ID
INSPECTION_FLAG
TIMELINE
INSPECTIONS:
ACCOUNT_ID
INSPECTION_DATE
INSPECTION_PERIOD
Here you have the table with data:
ACCOUNT_ID | INSPECTION_DATE (DD/MM/YYYY) | INSPECTION_PERIOD (YYYY/MM) | TIMELINE | INSPECTION_FLAG |
---|---|---|---|---|
001 | 11/01/2011 | 201106 | 1 | - |
001 | 11/01/2011 | 201106 | 2 | - |
001 | 11/01/2011 | 201106 | 3 | - |
001 | 11/01/2011 | 201106 | 4 | - |
001 | 11/01/2011 | 201106 | 5 | - |
001 | 11/01/2011 | 201106 | 6 | 1 |
001 | 11/01/2011 | 201106 | 7 | - |
001 | 11/01/2011 | 201106 | 8 | - |
001 | 11/01/2011 | 201106 | 9 | - |
001 | 11/01/2011 | 201106 | 10 | - |
002 | 15/05/2012 | 201203 | 1 | - |
002 | 15/05/2012 | 201203 | 2 | - |
003 | 08/07/2012 | 201204 | 1 | - |
003 | 08/07/2012 | 201204 | 2 | - |
004 | 12/09/2013 | 201305 | 1 | - |
What I need is to get those ACCOUNT_ID that have an INSPECTION_FLAG = 1.
And then show the ACCOUNT_IDs with all the corresponding information (INSPECTION_DATE, INSPECTION_PERIOD, TIMELINE).
For the example I gave, I should get the following:
ACCOUNT_ID | INSPECTION_DATE (DD/MM/YYYY) | INSPECTION_PERIOD (YYYY/MM) | TIMELINE |
---|---|---|---|
001 | 11/01/2011 | 201106 | 1 |
001 | 11/01/2011 | 201106 | 2 |
001 | 11/01/2011 | 201106 | 3 |
001 | 11/01/2011 | 201106 | 4 |
001 | 11/01/2011 | 201106 | 5 |
001 | 11/01/2011 | 201106 | 6 |
001 | 11/01/2011 | 201106 | 7 |
001 | 11/01/2011 | 201106 | 8 |
001 | 11/01/2011 | 201106 | 9 |
001 | 11/01/2011 | 201106 | 10 |
Do you know how could I do that?
Thank you!!!
I Assume there is a relation between the two tables with ACCOUNT_ID
Then Take the straight table and
Calculated Dimension:- If( INSPECTION_FLAG = 1, ACCOUNT_ID ) //And click on suppress when value is null
Dimensions:- INSPECTION_DATE, INSPECTION_PERIOD, TIMELINE
Expression:- 1
Maybe the easiest way is to get a table ACCOUNT with primary key ACCOUNT_ID and a flag telling you if the account has an inspection or not. (ACCOUNT_ID, INSPECTION_FLAG_ACCOUNT)
what is the timeline? is it a fact?