Creating custom associations

[#1]

Hello,

I am new to Qlik Sense.

Could anyone guide me as to how I can create my own association instead of using Qlik's analyzing engine?

I cannot figure out a way to create my own association.

Thanks,

SS.

[marcus_sommer · Accepted solution]

Associations will be made with the fieldnames. That meant you need to change the fieldnames if you want to create an association or to break one in the following manner:

OldName as NewName

Further helpful wille be: Get started with developing qlik datamodels.

- Marcus

[#3]

Hi Marcus,

That is what I figured and I did change the names accordingly.

This is a snippet of the data model. See the last columns in each of the table named as "Date", although they are prefixed with the table name.

What I was trying to understand is, is there no way that I can choose my own associations rather than taking Qlik's associative engine's recommendations?

P.S: I think I am intelligent enough to figure out the associations I need.

Marcus Sommer <span class="icon-status-icon icon-mvp" title="Mvp"></span> wrote:

Associations will be made with the fieldnames. That meant you need to change the fieldnames if you want to create an association or to break one in the following manner:

OldName as NewName

Further helpful wille be: Get started with developing qlik datamodels.

- Marcus

[#4]

Figured it out. I just had to remove the Table name which was prefixed.

Thanks Marcus. I marked your answer as correct answer.

I was trying to achieve the Billed table & the receipts table to be connected to my calendar master table so I can use the master Calender table as my dimension.

This is what I got after changing the script from Data Editor. Does this look right to you?

Testing on it.

[marcus_sommer]

I suggest to adjust the datamodel then you have now a synthetic key between your billed and receipts table because they have more then one field in common (Date and Bill ID). This musn't be technically wrong but it's best practice to resolve such automatically generated synthetic keys/tables with own solutions to ensure that you understand your datamodel and also knows the quality of your data: Synthetic Keys

In your case this could be a composite key like:

Date & '|' & [Bill ID] as BilledReceiptsKey

But it could be that these composite key covered not each case (maybe some dates and/or bill id's are in one table and not within the other or reverse) and you need to transfer some fields from table to another (per joining or mapping) and/or fill up missing data/links and/or to create a link-table between them. Further I could imagine that these dates couldn't be simply associated because they are more a chain of events and you need to harmonize them per Canonical Date.

I think you will need to dive deeper in your data to determine which data are related to eachother in which way - indepent from their origin fieldnames which might give an orientation but not more.

- Marcus

[#6]

I made a few more changes, this is what the key looks like.

Can you help me figuring out what exactly this means? Does it mean it has created a composite key of all this?(I hope not). How can I test this?

Besides, I cannot join the tables because if I do so, I cannot use these as a common dimension for both tables when I am filtering, Can I?

$Syn 1 = Date+Bill ID+Matter Name+Client Name+Client Group+Partner+City

[marcus_sommer]

Yes it meant that qlik has generated a composite key from all those fields. How to solve? Probably you need to do several things, for example to divide fields like Client Name, Client Group, Partner City into dimension-tables, creating of composite keys between them and/or creating of a link-table.

It won't be easy for as beginner but there is no way around then to learn these datamodel basics. For this I suggest like above mentioned to investigate your data carefully and to read the guidings within: Get started with developing qlik datamodels and the other links.

Further helpful to start is to going forward step by step - this meant to load at first one table and then examining it, then comes the next table which must be merged or associated and again it will be examinated within the gui per listboxes and charts and then follow the next table ... This ensured that you keep on overview and could learn and avoids an unsystematically trial and error approach with rather accidental results.

- Marcus

[#8]

Thanks again.

Wow you are super helpful!

However, this does look like it has solved my problem.

I can now use these columns as a master dimension integrating both tables.

I do not see any problems at the moment though. What implications can it cause?

[#9]

Oh and by the way, does not look like a composite key, looks more like individual keys, from what I see.