Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
qlikmeplease
Contributor II

Solution to Difficult Question

Hi,

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

Capture.PNG

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,

5 Replies

Re: Solution to Difficult Question

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

qlikmeplease
Contributor II

Re: Solution to Difficult Question

which two tables should i concatenate?

The FINAL GL along with the Salesfact?

Re: Solution to Difficult Question

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

qlikmeplease
Contributor II

Re: Solution to Difficult Question

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

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?

Community Browser