topic How to use Link table in Cross table in QlikView

How to use Link table in Cross table

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi Team,

The below tables have LOB,Team name, ED, Months are same however Volume table have facts as "Volumes " .............ReqdHC table have fact as "ReqdHC".

Unable to write Linktable for below due to Crosstable.

Volume:
CrossTable(Month, Volumes, 3)
LOAD
LOB,
[Team Name],
ED,
[42400],
[42428],
[42460],
[42490],
[42521],
[42551],
[42582],
[42613],
[42643],
[42674],
[42704],
[42735]
FROM

(ooxml, embedded labels, table is Volume);

ReqdHC:
CrossTable(Month, ReqdHC, 3)
LOAD
LOB,
[Team Name],
ED,
[42400],
[42428],
[42460],
[42490],
[42521],
[42551],
[42582],
[42613],
[42643],
[42674],
[42704],
[42735]
FROM

(ooxml, embedded labels, table is ReqdHC);

Re: How to use Link table in Cross table

swuehl — Sat, 06 Aug 2016 08:02:07 GMT

How do you want your final model to look like?

Do you want to concatenate the fact tables or link the fact tables by a key made of LOB, Team name, ED, Month?

Re: How to use Link table in Cross table

Anonymous — Sat, 06 Aug 2016 12:16:37 GMT

When I try to use link table, common fields (Lob, team, name, ED, Month) get concatenate and facts tables get added.

Ex.

Actual source

Lob, team name, Ed, month ( jan16, feb16......) month as cross table.

Table1 (cross table - month)

Lob, team name, Ed, month, volume

Table 2 (cross table - month)

Lob, team name, Ed, month, reqhc

Result,

Lob, team name, Ed, month, volume, reqhc

Note...month column is cross table.

Re: How to use Link table in Cross table

swuehl — Sat, 06 Aug 2016 17:42:40 GMT

I haven't understood why your tables get concatenated when you are trying to use a link table (which you need to create, or QV will create a synthetic table with the four common key fields for you).

The tables should not auto-concatenate, because the fact field names (volume and reqhc) are different.

But I would suggest like to suggest that you concatenate your tables after your made the CROSSTABLE transformation, so you might need to do a RESIDENT LOAD of your second table and apply the CONCATENATE LOAD prefix to do a forced concatenation. Then drop your second table (you concatenated a copy to the first, so no data are lost).

Something like

Table1:

CROSSTABLE (Month, Volume,3)

LOAD ...;

Table2:

CROSSTABLE (Month, Reqhc,3)

LOAD ...;

CONCATENATE (Table1)

LOAD * RESIDENT Table2;

DROP TABLE Table2;

Maybe you could also use a common fact field and an additional Type field:

Table1:

CROSSTABLE (Month, Fact,4)

LOAD LOB,
[Team Name],
ED,

'Volume' as Type,
[42400], ...

;

CROSSTABLE (Month, Fact,4)

LOAD

LOB,
[Team Name],
ED,

'Reqhc' as Type,

[42400], ...

...;

These two tables should get auto-concatenated.