master dates from multiple dates

[Mahamed_Qlik]

Hi All

I have below tables and I want to create master calendar which be considered with all the highlighed dates from all the table. what is best approach to achieve this.

I have tried with concatenating table using flag but my number of record count is almost duplicating.
What should be the best way to handle to this.

Note: I have already checked the canonical date post but it seems not suitable for me because I have only one key between tables that is "Store_ID"

Kindly suggest

Regards

Mhamed

[robert99]

Hi @Mahamed_Qlik 

NB. I assume there are many entries for each Store_ID for all three categories. So concatenating might be the easy (and best) option. I'm unsure what your reservation is unless it is a huge number of records. 

So I would concatenate all three tables. And do a canonical date from there. You would likely need a new field for the three tables. Like rowno() & 'Store' as RowID AND Rowno() & 'Staff' as RowID AND Rowno() & 'Prod' as RowID to join to the canonical date though. To ensure there is a unique reference for all the entries. And use RowID to join to the Canonical table

I tend to do this anyway as even when I thought I used a unique number to link to the canonical date table it turned out not to be. Rowno() and an one off identifier eg 'Store' for each table loaded always is.

[RafaelBarrios]

Hi @Mahamed_Qlik 

fllowing @robert99 suggestion, you should go for a canonical calendar 

https://community.qlik.com/t5/Design/Canonical-Date/ba-p/1463578

Hope this helps.

best,

users find answers! Don't forget to mark a solution that worked for you & to smash the like button! 🙂

[vinieme12]

all of these seem to be dimension tables

can you include your fact table as well? as  specify the cardinalities 1-to-n or 1-to-1