1 to N relationship

[#1]

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

A

B

C

D

Table 2

SubProd

AA

BB

CC

DD

Fact

Revenue

10

20

30

40

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...

[kaushiknsolanki]

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

[#3]

Hi Kaushik,

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

[kaushiknsolanki]

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   

[#5]

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.

[#6]

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.

[#7]

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..