Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
Not applicable

Help in Creating Bridge table

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

10 Replies

Re: Help in Creating Bridge table

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

Not applicable

Re: Help in Creating Bridge table

Can you please tel me the way to create bridge table

Re: Help in Creating Bridge table

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

Not applicable

Re: Help in Creating Bridge table

Can you provide sample QVW

Re: Help in Creating Bridge table

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;

Re: Help in Creating Bridge table

Find the attached file

Regards

Anand

ElizaF
Contributor II

Re: Help in Creating Bridge table

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.

Re: Help in Creating Bridge table

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

Not applicable

Re: Help in Creating Bridge table

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