Unable to Filter Because TableColumn Does Not Have All the Element Lists

[dune1914]

Hello To All,

We don't have access to the model from Framework Manager to directly see the table joins but we can see the SQL even though it is a bit confusing as for it is a long sql and lots of inner joins and 1 or 2 LEFT OUTER joins near the end of the SQL.

- We have this list report with columns such as :
TableA.Col1___TableB.Col2___TableC.Col3___TableD.C ol4

- TableB.Col2 and TableC.Col3 "almost" always have the same values in it.
- But sometimes TableC.Col3 is Null/Empty

- TableB.Col2 IS NEVER supposed to be empty

- We cannot filter on TableB.Col2 because it doesn't have all the element list values we want to filter on (x,y,z) even though it is never empty and is almost always he same as TableC.Col3

- So we need to Filter the report on TableC.Col3 column like this for example:
TableC.Col3 in (x, y ,z)

- Values from TableC.Col3 for example "x" are related to a specific TableA.Col1 value
Here is a report list display example:

===
TableA.Col1___TableB.Col2___TableC.Col3___TableD.C ol4
===
USA_____________x_________________x___________Hell o
USA_____________x_________________x___________To
USA_____________x______________(Empty)________All
USA_____________U_________________x___________Here
CAN_____________y_________________y___________Bye

- TableA.Col1 is a common (is related with) column between TableB.Col2 and TableC.Col3.
I mean that for the same TableA.Col1 (USA) value there is many times the same TableC.Col3

Question:
How do we manage to get all 4 USA records in my example shown above?
Is it possible to do?


Thanks to anyone willing to advice on this!

[dune1914]

Anyone ?

[hespora]

I believe you're not getting replies due to the somewhat convoluted way of describing the problem.

My most straightforward answer would be "Filter on [TableA].[ColA] = 'USA'", as you have not stated anything that tells me you can't filter on that.

[dune1914]

hespora,

1- Thanks for the reply
2- In fact TableA.col1 is actually a number ... I just put Countries as an example but in fact there is way too much TableA.col1 possible values that's why we can't really filter on it.

===
TableA.Col1___TableB.Col2___TableC.Col3___TableD.C ol4
===
123_____________x_________________x___________Hell o
123_____________x_________________x___________To
123_____________x______________(Empty)________All
123_____________U_________________x___________Here
456_____________y_________________y___________Bye
...

3- I'm trying to create a first Query1 with all appropriate filters in it including the filter TableC.Col3 in (x) for example so that way i will have all the possible TableA.col1 values.
4- Then i'm trying to create another Query based on the first one by simply drag and dropping Query#1 to the right of this new Query#2. I think the term is REFERENCE ?
5- I was exepecting Query#2 to inherit filters from Query1 but somehow it is still showing me values that should not be there.
6- I'm wondering if it it not BECAUSE of the real JOINS between tables in the model (we don't have access)
7- So now i'm not sure anymore how to handle this need.

[AnalyticsWithJay]

Create a data item with the following expression. Let's call it MyCalc for the sake of this example:

CASE WHEN col3 IS NULL
   THEN col2
   ELSE col3
END

Assuming relational, create a detail filter with the expression:

MyCalc = whatever value.

What this will do is filter on col3 when it's not null, else filter on col2.

[dune1914]

Thanks for the tip and that's exactly one of my first reflexes but as mentioned at the beginning of my post
  - We cannot filter on TableB.Col2 because it doesn't have all the element list values we want to filter on (x,y,z) even though it is never empty and is almost always he same as TableC.Col3

:(

[AnalyticsWithJay]

The requirements are not very clear  :-\. Which combination of columns allow you to filter? Once you provide some logic we can come up with a solution.

If Col2 doesn't have all the elements, and Col3 does -- but col3 could be null, would it not satisfy your requirement to filter on col3, and in cases where it is null, filter on col2?

[dune1914]

The problem is that col2 might be different than col3. Almost all the time they are the same but we cannot afford to loose those records when col2 and col3 aren't the same.    :-[
Take a look at the 4th row in my data example. U is not part of the element list we are interested in.

[AnalyticsWithJay]

And when they are not the same, which is the correct value? How do you decide which to pick? This is the requirement that is missing.

If you don't know which to pick then your data is bad. You can only work with the data you have.

[dune1914]

===
TableA.Col1___TableB.Col2___TableC.Col3___TableD.C ol4
===
123_____________x_________________x___________Hell o
123_____________x_________________x___________To
123_____________x______________(Empty)________All
123_____________U_________________x___________Here


In fact, the column that relates all those 4 out of 5 records (even the one where TableC.Col3 is empty ) is TableA.Col1

Bu i'm missing ideas as for now. Is this even possible ?

[Lynn]

===
TableA.Col1___TableB.Col2___TableC.Col3___TableD.C ol4
===
123_____________x_________________x___________Hell o
123_____________x_________________x___________To
123_____________x______________(Empty)________All
123_____________U_________________x___________Here


In fact, the column that relates all those 4 out of 5 records (even the one where TableC.Col3 is empty ) is TableA.Col1

Bu i'm missing ideas as for now. Is this even possible ?

Doesn't sound like a very good job was done on the Framework Manager model.

The first response you got was to filter on TableA.Col1, but you dismissed that idea.

I don't think you adequately asnwered CognoidJay who, I believe, is asking you to clarify the appropriate filter criteria requirement. You said you want to filter on TableC.Col3 in (x, y, z) which will give you all three rows in your latest example except the empty one. If the empty one is also needed then what is the basis for including or excluding null values in TableC.Col3?

[dune1914]

Lyn,

be "Filter on [TableA].[ColA] = 'USA'",

I know and I also brought about that filter on the first column.

The problem is we don't know the values of colA as for there are too many but we do know the values of colC (x,y,etc)

[Lynn]

Lyn,

be "Filter on [TableA].[ColA] = 'USA'",

I know and I also brought about that filter on the first column.

The problem is we don't know the values of colA as for there are too many but we do know the values of colC (x,y,etc)

You still haven't answered the question:

You said you want to filter on TableC.Col3 in (x, y, z) which will give you all three rows in your latest example except the empty one. If the empty one is also needed then what is the basis for including or excluding null values in TableC.Col3?

CognoidJay has asked twice already. I asked in my previous post and I am now asking for a second (and final) time.

[dune1914]

Sorry for the delay.

I'm not sure if I understood the question correctly but here it goea'

THE RECORD WHERE THERE IS A NULL VALUE IN THE TABLEc.COL3 is supposed to be shown BECAUSE IT HAS THE SAME tableA.Col1 value. That is 123.

Hope this answers both your question.