Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
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
Highlighted

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

Highlighted
Not applicable

Can you please tel me the way to create bridge table

Highlighted

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

Highlighted
Not applicable

Can you provide sample QVW

Highlighted

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;

Thanks & Regards,
Mayil Vahanan R
Highlighted

Find the attached file

Regards

Anand

Highlighted
Creator II
Creator II

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.

Highlighted

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

Highlighted
Not applicable

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