Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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);
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?
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.
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.