Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have 3 tables (I have simplified the data) : "Transactions" and "Stations" and "Cardlock".
I want to show Stations that are not present in table "Cardlock" and that do not have Transactions.
I tried to achieve this in a chart table with a set analysis and a count formulas but I am still not able to make it works.
I don't want to change my tables in the script, I want to use set analysis or solution other than script.
How to show in a chart table, Stations that are not present in table "Cardlock" and that do not have Transactions?
Transactions
Date_Tr | Station | Amount |
---|---|---|
2014-01-12 | 007 | 100 |
2014-03-06 | 121 | 200 |
2014-05-23 | 007 | 300 |
Stations
Station | Name |
---|---|
007 | Montreal |
084 | Quebec |
121 | Laval |
171 | Boston |
186 | Hull |
202 | Ottawa |
Cardlock (I only have cardlock in that table)
Station | Flag_Cardlock |
---|---|
084 | Yes |
171 | Yes |
Results of what I want to show!!!!
Station | Name | Count Stations without Transaction |
---|---|---|
186 | Hull | 1 |
202 | Ottawa | 1 |
Total | 2 |
Thank you!
Patrick
I found a better one:
=sum({<Station=E({<Date_Tr={*}>}) * E({<Flag_Cardlock={Yes}>})>} 1)
Hi Patrick,
maybe something like this:
=sum({<Station=E({<Date_Tr={*}>}), Name=E({<Flag_Cardlock={Yes}>})>} 1)
- Ralf
Thanks Ralf!
Your solution works in my simplified sample. In my true model too as I had to add more restrictions.
I was able to find a solution just after my post but I will try to simplify my set analysis formula like yours.
I found a better one:
=sum({<Station=E({<Date_Tr={*}>}) * E({<Flag_Cardlock={Yes}>})>} 1)