Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I would like to allow a user to choose values from a single filter dimension and apply the selection to two columns with OR logic as opposed to AND. For example:
Account Number | Primary Officer | Secondary Officer |
123 | A | B |
456 | C | A |
789 | B | C |
012 | A | A |
An officer can be the primary or secondary officer (or both) of an account. I would like to have a single "Officer" filter dimension that a user can choose from to display all accounts an officer is associated with, whether it be primary or secondary (or both). So if a user chose "Officer A" from the filter it should result in the following:
Account Number | Primary Officer | Secondary Officer |
123 | A | B |
456 | C | A |
012 | A | A |
All of the data resides in the same source table (QVD). So I don't mind doing the necessary logic in the data load editor or via Master Items.
You could create an auxiliary table that helps you to group, for example:
table_aux:
load accnro,
official1 AS official_aux
resident table 1;
concatenate (table_aux)
table_aux:
load accnro,
official2 AS official_aux
resident table 1;
final:
load distinct
accnro, // key
official_aux as official_final
resident table_aux;
drop table table_aux;
The "final" table will be related by accnro to your fact table, you should use the "official_final" field as a filter, that will work for your OR.
regards!!!!
You could create an auxiliary table that helps you to group, for example:
table_aux:
load accnro,
official1 AS official_aux
resident table 1;
concatenate (table_aux)
table_aux:
load accnro,
official2 AS official_aux
resident table 1;
final:
load distinct
accnro, // key
official_aux as official_final
resident table_aux;
drop table table_aux;
The "final" table will be related by accnro to your fact table, you should use the "official_final" field as a filter, that will work for your OR.
regards!!!!
Perfect! This did the trick quite nicely. Thank you!