topic Re: Help in Creating Bridge table in QlikView

Help in Creating Bridge table

Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Help in Creating Bridge table

its_anandrjs — Wed, 16 Nov 2016 04:43:11 GMT

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

Re: Help in Creating Bridge table

Wed, 16 Nov 2016 08:36:14 GMT

Can you please tel me the way to create bridge table

Re: Help in Creating Bridge table

its_anandrjs — Wed, 16 Nov 2016 08:44:35 GMT

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

Re: Help in Creating Bridge table

Wed, 16 Nov 2016 10:39:51 GMT

Can you provide sample QVW

Re: Help in Creating Bridge table

MayilVahanan — Wed, 16 Nov 2016 10:52:17 GMT

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

its_anandrjs — Wed, 16 Nov 2016 11:02:53 GMT

Find the attached file

Regards

Anand

Re: Help in Creating Bridge table

ElizaF — Wed, 16 Nov 2016 11:04:05 GMT

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,

......

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

its_anandrjs — Wed, 16 Nov 2016 11:11:51 GMT

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

Re: Help in Creating Bridge table

Wed, 16 Nov 2016 12:24:21 GMT

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

Re: Help in Creating Bridge table

ElizaF — Thu, 17 Nov 2016 07:29:36 GMT

Use Concatenate(Linktable) function. Also you must to have in the table "LinkTable" one KEY filed with the FACT1 and FACT2 tables.

From what I noticed the "Medical ID" and "Account ID" fields are found in all of this three tables. So the connection field can be [Medical id]&'#'&[Account ID] AS KEY.