topic Re: How to join multiple tables while maintaining parent and child relationship in Connectivity & Data Prep

How to join multiple tables while maintaining parent and child relationship

Omkar13 — Wed, 09 Jun 2021 04:18:12 GMT

Hello Qlik Experts,

I m trying to combine multiple table while maintaining relationship between them. How should i achieve this? PFA data model for reference

Re: How to join multiple tables while maintaining parent and child relationship

Sammy_AK — Sat, 12 Jun 2021 14:23:33 GMT

can you provide more details? in your screen shot, i see tables are already associated. what do you mean by combine multiple tables ? are you referring to joining 2 or more tables here ?

Re: How to join multiple tables while maintaining parent and child relationship

Omkar13 — Tue, 22 Jun 2021 01:03:17 GMT

Thanks @Sammy_AK for the reply.
I want to create a master table from above 4 table, e.g an All Issues table while still maintaining the atomicity. How can I achieve that? What do you think of this approach?
With one table, I can write better set analysis expression and create better charts, which is not the case now. 😞

Re: How to join multiple tables while maintaining parent and child relationship

Sammy_AK — Tue, 22 Jun 2021 10:08:22 GMT

You would either need to join the data using join / keep in the load script so that you have one big table or Concatenate data sets into one table by providing similar alias name and flag column to differentiate the data.

the above association is also good enough to use set analysis and create charts as good as any other. you can use a variables and make set analysis more dynamic. if there is any specific scenario where you are unable find a solution ?

Re: How to join multiple tables while maintaining parent and child relationship

Omkar13 — Thu, 24 Jun 2021 01:15:33 GMT

Thanks @Sammy_AK

I want to have a date range option on my dashboard. Each table have 5 date fields. So I created a bridge for each table and calendar.

[Table Theme]> [Bridge]> [CalendarTheme]

[Table Feature]> [Bridge]> [CalendarFeature]

[Table Epic]> [Bridge]> [CalendarEpic]

[Table Story]> [Bridge]> [CalendarStory]

I was hoping if I have one table, than creating master calendar will be much easier and use. The problem now is how to create a bridge between four master calendar and create a Grandmaster Calendar.

Also I'm not sure if the above model is scalable as I plan to get different data set (At least 4) to the existing one. Hence I was finding a way to combine 4 table while maintaining the relationship as all the fields are same and use Issue Type to differentiate the data, in a similar way of how Canonical Date works.

What do you think?

Re: How to join multiple tables while maintaining parent and child relationship

Sammy_AK — Thu, 24 Jun 2021 18:42:38 GMT

having multiple calendars you cannot control the data on dates. as you will have multiple date columns, instead all the tables should be pointed to one calendar where you can control the date field better.

Master calendar as dimension helps you split date into multiple columns like Date, Month, Year, Week, QTR etc. if you link Date of master calendar to the other tables you can base-line the data on single date column.

Re: How to join multiple tables while maintaining parent and child relationship

Omkar13 — Wed, 07 Jul 2021 05:52:49 GMT

Hi @Sammy_AK

I was able to follow your advice and update my model.

Thanks for your help.