Link table issue

[#1]

I have a data model using a link table. Now there are fact table have retail data and fact table having non-retail data.

This link table has got concatenated keys.... retail fact table: key1& key2&key3 where key3 is key for retail.

                                                                  Non retail fact table: key1& key2&key4 where key4 is key for retail.

Now i have got a master affiliation file which is having association for retail fact table and Non retail fact table. this table is have key3  and key4 association.

How to redesign data model for this affiliation file.

Thanks

khushboo

[avinashelite]

please share the sample data set and the required output , so that we could help you with the same

[#3]

Hi Avinash,

Thanks for your reply.

I have tried to replicate the issue in attached file.

Issue being when i select Acc , my trx and units metric blank out. How to resolve this issue?

[avinashelite]

I checked the file , the link of tables is broken .

In the fact1 and fact 2  you have key1 and key2 common so you could try like this ..

Key3:

mapping LOAD

Key1&'-&Key2 as Key

Key3

from fact1;

Key4:

mapping LOAD

Key1&'-&Key2 as Key

Key4

from fact2;

Fact1:

LOAD

Key1,

Key2,

Applymap('Key4',Key1&'-&Key2,'NA') as Key4,

Key3

from fact1;

concatenation

LOAD

Key1,

Key2,

Applymap('Key3',Key1&'-&Key2,'NA') as Key3,

Key4

from fact2;

// after this you follow the rest it will help you to resolve the issues

[#5]

Avinash, Thanks for your help.

But i did not understand the solution.

Can u attached a working qvw.

[avinashelite]

I don't have the excel files to reload , common keys are missing because of which the linking of tables is not proper ...Once I get time will try to build the app and share with you