topic Re: Select date in two fields in QlikView

Select date in two fields

qlikwiz123 — Sat, 16 Nov 2024 00:00:16 GMT

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?

Re: Select date in two fields

edwin — Wed, 09 Sep 2020 21:10:28 GMT

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

Re: Select date in two fields

qlikwiz123 — Wed, 09 Sep 2020 21:17:23 GMT

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

Re: Select date in two fields

dapostolopoylos — Thu, 10 Sep 2020 08:44:42 GMT

Check out the solution that i uploaded.

It is based on the trigger functionality

Re: Select date in two fields

qlikwiz123 — Thu, 10 Sep 2020 13:23:49 GMT

Hi,

I did this too, but Triggers don't work on Access Point (For most part). So this is not reliable

Re: Select date in two fields

edwin — Thu, 10 Sep 2020 13:43:49 GMT

You missed one critical point, dont create two tables, concatenate them

Re: Select date in two fields

qlikwiz123 — Thu, 10 Sep 2020 14:49:10 GMT

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

Re: Select date in two fields

Brett_Bleess — Wed, 23 Sep 2020 11:02:36 GMT

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

Re: Select date in two fields

edwin — Tue, 15 Dec 2020 21:15:12 GMT

But if I concatenate, the rows will be doubled.

your rows ARE already doubled albeit in two separate tables.