topic Re: 1 to N relationship in QlikView

1 to N relationship

Mon, 15 Jul 2013 03:14:10 GMT

Hi All,

I need your suggestion for establishing set analysis expression.

There is a scenario like below..Two dimension tables(table 1 and table 2) and a fact table.. There are other fields also , but I have mentioned required. Dimensions are linked fact table with its ID's. ProdID and SubProdID which I have not shown below...Please assume the same.

Table1

Prod

Table 2

SubProd

Fact

Revenue

The relation between Table 1 and Table 2 are 1-N..

A had relation with AA,BB,CC,DD

B had relation with BB,CC

C had relation with AA,CC

D had relation with AA,DD

Can you please provide the expression for the same using set analysis...

Re: 1 to N relationship

kaushiknsolanki — Mon, 15 Jul 2013 06:37:19 GMT

Hi Sunil,

I am sorry but I couldn't understand your problem.

Kindly explain with example, if possible also post sample qvw file.

Regards,

Kaushik Solanki

Re: 1 to N relationship

Mon, 15 Jul 2013 07:46:07 GMT

Hi Kaushik,

I have attached excel screenshot. There I have shown dummy data. There I need to establish relationship b/w two dimension tables..

Re: 1 to N relationship

kaushiknsolanki — Mon, 15 Jul 2013 08:26:45 GMT

Hi Suneel,

Give the dummy data in QVW file.

Which will exactly help in creating same problem like you.

As you have shown in the screenshot, the relation is not static meaning, A links to all 4 values where as B links to only 2 , same C links to only 2 and D to only 1.

There should be some table which defines which will link to what.

Regards,

Kaushik Solanki

Re: 1 to N relationship

Mon, 15 Jul 2013 10:36:35 GMT

Hi Kaushik,

Since I do not have qlikview installed in my current machine , so sending excel file. In that I have already made some links between 3 tables. Now only this is that we need to establish relationship between 2 tables.(Table1 and Table2)

Hope it clears the problem.

Re: 1 to N relationship

Mon, 15 Jul 2013 10:56:18 GMT

Hi,

No need to relate the table1 and table2 in qlikview, so that pls have like as u have in excel.

If we relate the table 1 and 2 then it creates the Circular reference.

Regards,

Kabilan K.

Re: 1 to N relationship

Mon, 15 Jul 2013 11:09:12 GMT

Prod field of Table1 has the relationship with SubProd field of Table2..

Its not like external linking between two fields but has the relation just. So no circular reference.

Something like below expression... But this one need to implement by set analysis by using or and operators.

If(Prod={A} and SubProd={AA,BB,CC,DD},sum(Revenue),if(Prod={B}...........goes in this way..