Doubt with associations

[Anonymous]

Hi,

I have the following tables

Table One

num     letter

1          A

2          B

3          C

4          D

5          E

Table Two

letter          Equip. Number          Date

A               12454752               21-12-2008

C               54648524               02-10-2009

D               45762378               14-03-2010

A               78964343                08-05-2011

Both tables are associated by the column letter. When I don't select data by Date I manage to get all the values of letter in table One, but once I select a date interval I only get values A, C and D for letter (as is in table two). How can I keep obtaining all the values of letter in table one? Even if their count in table two is zero?

It seems pretty obvious to me why it isn't working but I just can't figure out how to make it work or if it is even possible to make it work.

Thanks,

[kuczynska]

I think you have at least a couple of options, but it all depends what is the final requirement for your app, so it's very hard from my perspective to find the right solution for you. Nevertheless - good luck, hope the below will help at least any how.

1. Currently your two tables are associated by column 'letter'. If you will rename 'letter' to something else in one of the tables you will break the link between two tables and any selections made in 'Date' will not affect values in Table One.

2. You can concatenate Table Three to your Table Two, where Table Three has the same structure as Table Two with "missing" letters initiated and Equip. Number and Date columns set as 'N/A' or something else. In that situation tables Table One & Table Two (Table Three is concatenated to Table Two, so it doesn't exist as a separate object) are still associated or:

3. you can complete the same steps as point 2 but you can try left join/outer join Tables One and Two, it's up to you.

Not sure if this will help you... maybe give us some extra information on the requirements and how Table One and Table Two are associated with the rest of your data model.

[Anonymous]

Thank you!

I'll try those actions.

[agigliotti]

what's exactly your UI requirements ?

what chart object and dimensions/measures are you using ?

[Anonymous]

What I need is that the letters in Table One aren't ignored if they don't appear in table two.

On my actual Table One, I have a list of equipments, and table two is made of the list of malfunctions for those equipments. However some equipments haven't yet malfunctioned and therefore do not appear on table one or on the table or are counted on a bar chart I build for visualization. This is because everytime I apply a filter only the equipments that have malfunctioned are selected since table one has no date information and the date selection is made only on table two. Nonetheless I need the equipments that have not malfunctioned to have a count of 0 occourrences in table two.

I have plenty more of variables but they are:

Dimensions: Boundary

Measure: If (IsNull(Aggr(Count(Ordem) OR 0, [Equipamento-Num. SAP])), 0, Aggr(Count(Ordem), [Equipamento-Num. SAP]))

Chart Object: Table

[agigliotti]

i guess it's not an issue rather it's works as expected using QIX associative engine.

[kuczynska]

I agree.

[Anonymous]

Yes it works as expected. I am aware of that. However on a table visualization I would need to see the equipments with zero counts as well.

Got what you meant. Already changed the discussion title.

[kuczynska]

In that case you have to initialize all of your missing "letters" in the second table, i.e. follow approach 2 or 3.