building data model

**Tuanfeng** · 08-28-2014, 03:58 AM

Dear all,

I am seeking for some support on how to load raw data into a new structured workbook. I've attached two sample files for your convenience (Commerce hub export is the input file and the data model template is the output file), and basically what I want to achieve is threefold.

1. Loading data: The first four columns of the input file go to the "Contract_Fact" worksheet of the output file, whereas the "Tech Manager" field goes to the "Dim_Tech_Manager" worksheet and the "Contract Party" goes to the "Dim_Contract_Party" worksheet in the output file.

2. Sorting data: in the two "Dim" worksheets of the output file, keep the unique records only and give each a sequential ID

3. Matching data: Do a v-lookup in the "Contract_Fact" worksheet, to indicate for each record, the IDs of its tech manager and contract party.

Could you please help me out on this? Thank you very much in advance!!!

Regards,
Tuan

**mehmetcik** · 08-28-2014, 06:22 PM

Hi

This Macro meets the first part of your requirement

Please Login or Register  to view this content.

**mehmetcik** · 08-28-2014, 06:23 PM

Please explain what you mean by Unique IDs for sheets 2 and 3.

**Tuanfeng** · 08-29-2014, 03:04 AM

Hi Mehmetcik,

Thanks for the reply! Although when I run the codes, an error occurred, saying "1004, application-defined or object-defined error". Can you help me solve it?

As for my second and third point, in the original input file, the records under "Tech Manager" dimension and "Contract Party" dimension are not unique, meaning that one tech manager might be in charge of multiple contracts and therefore his/her name would appear multiple times. What I would like to achieve is basically for each dimension, find and keep all unique values, keep them in a separate sheet, and give each value an ID (starting from 1), and finally in the Contract_Fact sheet, do a v-lookup to indicate for each record, the IDs of its tech manager and contract party. (e.g. the column "Tech Manager" in the current input file will be replaced by "Tech_Manager_ID")

I hope this clarifies the requirement a bit more. My apologies for using unique value for each field in the sample file.

Look forward to your reply!

Regards,

building data model

LinkBack

Thread Tools

Rate This Thread

Display

building data model

Re: building data model

Re: building data model

Re: building data model

Thread Information

Users Browsing this Thread

Similar Threads

Creating a Pyramid Hierarchy structure from a flat structure

Form: Load Picture from Worksheet instead of a folder structure

"convert" data with logfile structure to spreadsheet structure

Data structure?

[SOLVED] data structure and my problem

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions