Joining customer data - problem - ideas?

[datanibbler]

Hi,

I am working on compacting a datamodel - aggregating it on a specified level of granularity - and at the same time merging all the tables into one by JOINing or MAPping, whatever is possible.

Until now, there was no issue with this. I now have two tables remaining in my datamodel - I want to make it one because I then have to store it into a qvd again so it can again be LOADed for the management_dashboard.

However, now there is an issue because now I have values in my central table and of course, if the number of records was to change now, that would be an issue. The key, however, is not unique on either side - that is quite normal in the central fact table, but in the dimension_table I don't have a unique key, either ... that's logical for I'm dealing with ShipTo_data, and one document, say an invoice, can list several receiving parties (only one payer). But that doesn't make it less of a problem ...

I cannot think of any way I can get out of this without multiplying the nr. of records in the fact table, so I am just going to do it that way and lateron I will look for records that are identical but for these few fields and in all but one of those I will simply null the values. That way, I will still have to be careful when I have any COUNTs in my visualizations because these duplicates should not be counted.

Can anyone think of another way that could be achieved? My solution is still somewhat crooked ...

Thanks a lot!

Best regards,

DataNibbler

[kfoudhaily]

I suggest creating link table instead:

Concatenate vs Link Table

[datanibbler]

Hi,

well, that would be a possibility, but then I can just leave the two tables standing as they are, no? The fact would remain that I have one document - mostly, those are invoices - being linked to about 20 receivers in some instances. The question is, what would be better from a business perspective, or more honest - just generating a number and saying in the GUI_app, like "this is linked to several receivers, but it has only one value" and telling the user to take care or to split the values among the receivers so the linking becomes unique (but that split would not have any logical reason but for this) ...

[datanibbler · Accepted solution]

Ah,

now has the Groschen finally fallen 😉

I just realized that this problem which has kept me thinking several hours is in fact not a problem: In SAP, which is where all my data comes from, the association of each position of an invoice with exactly one receiver is there. The tables and the link between them is the same that we recreate in Qlik, so all I have to do is go back one step into the creation of the Linktable and edit the key, instead of only using VBELN, I have to use VBELN&POSNR, and voila, I have a unique key and a 1:n relationship between the dimension table and my fact table ...

Sometimes it just takes a while until one has an idea that seems obvious in hindsight and it just has you wondering why you didn't think of it earlier ...

Best regards,

DataNibbler