Help in Creating Bridge table

[#1]

Hi,

I have  two fact tables i want to create an associate  between two fact tables through bridge table.Please help me to create a bridge table for that.

Finally i want connect the bridge table to  bellow link table in model. please find the attachment for sample data.

Linktable

Call ID

Medical id

Account ID

Event Id

Message id

segment id

Thanks in advance

[its_anandrjs]

Try to load your fact table this way and create the bridge table

Load

Medical id &'-'& Account ID as Key,

Call,

Call Status,

Medical id,

Account ID,

Call Name

From Location;

Load

Medical id &'-'& Account ID as Key,

Event Id,

Event Status,

Medical id as Medical_ID,

Account ID as Account_ID,

Event Name

From Location;

Regards

Anand

[#3]

Can you please tel me the way to create bridge table

[its_anandrjs]

You have to create the common link key between this two fact table then they will connect to each other,

I will suggest you on the below post.

Regards,

Anand

[#5]

Can you provide sample QVW

[MayilVahanan]

Hi

try like this

Fact1:

LOAD [Call ID],

     [Call Status],

     [Medical id],

     [Account ID],

     [Call Name],

     [Medical id] &'-'& [Account ID] as Key

FROM

(ooxml, embedded labels, table is Fact1);

Fact2:

LOAD [Event Id],

     [Event Status],

     [Medical id],

     [Account ID],

     [Event  Name],

     [Medical id] &'-'& [Account ID] as Key

FROM

(ooxml, embedded labels, table is Fact2);

LinkTable:

LOAD Distinct

  [Medical id],

  [Account ID],

  Key,

  'Fact1' AS Flag

Resident Fact1;

LOAD Distinct

  [Medical id],

  [Account ID],

  Key,

  'Fact2' AS Flag

Resident Fact2;

DROP Fields [Medical id],

  [Account ID] from Fact1, Fact2;

[its_anandrjs]

Find the attached file

Regards

Anand

[ElizaF]

Hi,

Please , see below:

FACT1:

LOAD

    [Medical id]&'#'&[Account ID]AS  KEY,

    [Call ID],

//    [Medical id],

//    [Account ID],

    [Call Status],

      [Call Name]

FROM

[Bridge table.xlsx]

(ooxml, embedded labels, table is Fact1);

FACT2:

LOAD

[Medical id]&'#'&[Account ID] AS  KEY,

    [Event Status],

//    [Medical id],

//    [Account ID],

    [Event  Name]

FROM

Bridge table.xlsx]

(ooxml, embedded labels, table is Fact2);

Linktable:

LOAD

IDMedical&'#'&idAccount AS KEY,

*

;

LOAD

Call,

IDMedical,

idAccount,

IDEvent,

IdMessage,

idsegment,

id

......

Concatenate(Linktable)

LOAD DISTINCT

KEY,

SubField(KEY,'#',1) AS [Medical id],

SubField(KEY,'#',2) AS [Account ID]

Resident FACT1;

Concatenate(Linktable)

LOAD DISTINCT

KEY,

SubField(KEY,'#',1) AS [Medical id],

SubField(KEY,'#',2) AS [Account ID]

Resident FACT2;

My recommendation is to have only one FACT table (Fact 1 and Fact2).

For example in FACT1 add the values from FACT2 with the applymap function.

I hope to help you.

[its_anandrjs]

For the Link Table concept use this type of load script also

Fact1:

LOAD [Medical id]& [Account ID] as Key,

     [Call ID],

     [Call Status],

     [Medical id],

     [Account ID],

     [Call Name]

FROM

[Bridge table.xlsx]

(ooxml, embedded labels, table is Fact1);

Fact2:

LOAD [Medical id]& [Account ID] as Key,

     [Event Id],

     [Event Status],

     [Medical id] as [Fact2 Medical id],

     [Account ID] as [Fact2 Account ID],

     [Event  Name]

FROM

[Bridge table.xlsx]

(ooxml, embedded labels, table is Fact2);

tmpLink:

LOAD

Key,

[Medical id],

[Account ID],

'Fact1' as TableName

Resident Fact1;  

Concatenate(tmpLink)

LOAD

Key,

[Fact2 Medical id] as [Medical id],

[Fact2 Account ID] as [Account ID],

'Fact2' as TableName

Resident Fact2;  

DROP Field [Medical id],[Account ID];

Regards,

Anand

[#10]

Thanks all.

i need to create a bridge table for this.

i already have link table in the Model.

I have  two fact tables i want to create an associate  between two fact tables through bridge table.

Finally i want  to connect this bridge table to   below link table in model.

Linktable

Call ID

Medical id

Account ID

Event Id

Message id

segment id