How to merge two datasets without losing original values

[posywang]

Hello,

We have two databases and one database has ResourcePool information and the other one doesn't. We were able to figure out a way to map that field and merge the data from two sources with calculated FTE. However, only a couple of reports in our dashboard needs that field and we prefer to use the unprocessed FTE than the calculated FTE for these reports.

We are thinking of adding original FTE back to the table since creating additional QVDs for original FTE will double the size of current dataset. The challenge is that we added additional rows to database if a resource is in two resource pools of a given month. Does our approach make sense? Is there a better way to handle it? Thank you very much for your help in advance!

Sample qvw is attached.

[vvira1316]

Hi,

may be you can create a key field using concat of following fields and have key in both tables but common fields only

in master/main table. Thus you will have join between two tables and will have link to use them.

ShortName

ResourceType

RespCd

MthNo

Year

LOAD ShortName & ResourceType & RespCd & MthNo & Year as TableKey
,ShortName
,ResourceType
,RespCd
,MthNo
,Year
,ResourcePool
,Percentage
FROM
[DataFile.xlsx]
(ooxml, embedded labels, table is Sheet1);

LOAD ShortName & ResourceType & RespCd & MthNo & Year as TableKey
,FTE
FROM
[DataFile.xlsx]
(ooxml, embedded labels, table is Sheet2);