Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have two different tables
TableA:
ID,
Sale Date
Amount
TableB:
ID,
Dividend Date,
Source
Both tables are connected using 'ID' as key.
I want to enable users to select a date in 'Sale Date' and the same date has to be selected in 'Dividend Date'. When this happens, the user see all the Amount for the specific date and Source for the same date.
How do I do this without connecting Sale Date and Dividend Date as this creates Synthetic keys?
given the limited information on requirements, i would say a quick way to solve this is instead of these being two separate tables, concatenate them and add a new field say FactSource to differentiate the two data sets. lots of possible spin off solutions after that.
1 you can rename both date fields as DATE - thus you have a single date field;
2 or you can have separate date fields and build a bridge to the calendar and in the bridge add a DATE TYPE field.
if you are not comfortable with the bridge try the solution #1, also given the limited requirements, id say sol #1
Hi,
1 you can rename both date fields as DATE - thus you have a single date field;
I can't do this as both tables already have 'ID' in common, thus making it as a key. If I rename both date fields as DATE, then it will be a synthetic key
Check out the solution that i uploaded.
It is based on the trigger functionality
Hi,
I did this too, but Triggers don't work on Access Point (For most part). So this is not reliable
You missed one critical point, dont create two tables, concatenate them
Hi,
I got that. But if I concatenate, the rows will be doubled.
Every ID will have two rows, one with Sale Date and another with Dividend Date. But if this is the only way, I may have to think
Couple of Design Blog posts that may be helpful in this case:
https://community.qlik.com/t5/Qlik-Design-Blog/Synthetic-Keys/ba-p/1472634
https://community.qlik.com/t5/Qlik-Design-Blog/Concatenate-vs-Link-Table/ba-p/1467569
https://community.qlik.com/t5/Qlik-Design-Blog/Canonical-Date/ba-p/1463578
Hopefully those may help you move it forward further.
Regards,
Brett
But if I concatenate, the rows will be doubled.
your rows ARE already doubled albeit in two separate tables.