topic Re: Solution to Difficult Question in QlikView

Solution to Difficult Question

Anonymous — Thu, 07 Sep 2017 11:16:42 GMT

Hi,

I will try and keep this as simple as possible. I have the below datamodel.

I have two seperate sources of data. And the only link i could establish between them was a FinancialYearMonth key that i created.

The issue is , that the relationship is not good enough , and creates problems within my data when calculating formulas.

The conflict between two dimensions that are not related is the cause of the issue.

The calculation i use is the below: however i need to present it in a straight table using the Product group as a dimension.

My set analysis is as follows : Sum({$<Category = {'Flour'}>}Amount)

This means that without a decent relationship between the values, the results are invalid.

Can anyone recommend a solution to the problem, the issue here is, i wouldve liked to create a unique key ,consisting of FinYear&Month&Productgroup as Key but the dates are all in the calendar table, and product group is in a seperate table altogether.

If i had done this, the link would be easy, as in the other table, the dates and classification are in the same table, meaning that FinYearMonth&Classification would be easy to accomplish. Bare in mind that classification and product group are the same. i just need the relationship to be on the level of product group.

Hope the above provides enough detail. I was thinking a link table but as I have never used one, im not sure whether that solution would be applicable with the above.

Any advice or help would be appreciated.

Regards,

Re: Solution to Difficult Question

sunny_talwar — Thu, 07 Sep 2017 11:24:31 GMT

You def. need a link table here... but before you do that, you will have to pull the above fields in a single table so that you can create a link table... or you can add Join FinMonthYear within DimInventory and create a keyfield within it and do the same on the FINALGL side and then break the join between calendar and FINALGL and link FINALGL and DimInventory

Re: Solution to Difficult Question

Anonymous — Thu, 07 Sep 2017 11:32:20 GMT

which two tables should i concatenate?

The FINAL GL along with the Salesfact?

Re: Solution to Difficult Question

sunny_talwar — Thu, 07 Sep 2017 11:34:52 GMT

Not concatenate, but link FINAL GL and Dim Inventory (ProductGroup sits in this table, right?)

Re: Solution to Difficult Question

Anonymous — Thu, 07 Sep 2017 11:40:59 GMT

Thats correct , yes it does. However, there is no way to link these two tables. See the Diminventory is crucial for the salesfact information.

Re: Solution to Difficult Question

sunny_talwar — Thu, 07 Sep 2017 11:44:20 GMT

I guess then bring ProductCode into SalesFact from DimInventory and bring FinMonthYear from calendar and then link SalesFact to FINAL GL by creating a key field?