| Kiamarz |
| Member |
|
| Motor City |
| Director of Technology |
|
| Male |
|
| Tuesday, October 2, 2012 |
| Thursday, November 5, 2015 8:49:39 PM |
27
[0.15% of all post / 0.01 posts per day] |
|
vlad wrote:This is not an answer to your question, but as a long time MapForce user just wanted to ask whether you have ever considered to stop speculating on when and whether some particular feature will be implemented in the language of your choice, and just take MapForce Server instead? Because it definitely covers ALL features, already now? I cannot imagine how many weeks of work it saved me...
Simply put, we don't have the capacity to buy a piece of software for one single purpose. We have had no issues automating mappings that do not use this one sort-by function.
|
I need help with 2 issues. I have provided my mfd file which is being built for c# generation and a screenshot to help understand the data structure. Don't be mislead by the mapping in the img b/c it is not implementing a proper solution.
First Issue
The source data structure contains assessment info with questions and answers by client ID along with the order of the questions and the value of the answer. Each assessment consists of several q&a's for several subject areas which are called domains. An example of the key fields as a comma delimited file look like so.
Code:
clientID, domain_description, question_order, answer_value,,,
500000, Nutrition Domain, 1, 0,,,
500000, Nutrition Domain, 2, 0,,,
500000, Nutrition Domain, 3, 3,,,
500000, Education Domain, 1, 0,,,
500000, Education Domain, 2, 4,,,
500000, Housing Domain, 1, 0,,,
500000, Housing Domain, 2, 2,,,
500000, Housing Domain, 3, 0,,,
500000, Housing Domain, 4, 5,,,
I do not care about the questions or answers. What I need is the [answer_value] of the MAX [question_order] for a particular [domain_description], [clientID] & [date]. Meaning I need to pull the final value of the domain for each clients assessment. The file contains many clients and many assessments for each client.
Second Issue
The normalized structure of the source data needs to be denormalized for the target. The source contains a record per domain; the target needs to be 1 record per date & client with all domain scores. I do not know how to do this without a denormalize function like some other ETL tools have.
|
@island Thank you so much for the you spent answering my questions. That is exactly what I was looking for. Thank you!
Could you help me understand why this solution works?
|
My mapping project is generating an XML file from a flat csv. The output is being generated in the sequence:
Code:<Agency> <AgencyName>Development Center</AgencyName> <aC_ProgramID>879</aC_ProgramID> <Site> <SiteName>Gompers-B25</SiteName> <Class> <ClassName>HS-103</ClassName> <DaysOpen> <Month>11</Month> <DateOpen>2014-11-25</DateOpen> </DaysOpen> </Class> </Site> </Agency> <Agency> <AgencyName>Development Center</AgencyName> <aC_ProgramID>879</aC_ProgramID> <Site> <SiteName>Gompers-B25</SiteName> <Class> <ClassName>HS-103</ClassName> <DaysOpen> <Month>11</Month> <DateOpen>2014-11-26</DateOpen> </DaysOpen> </Class> </Site> </Agency>
The only difference between the two records is the <DateOpen> date. My intention is for all values of <DateOpen> to appear under the same parent like so:
Code:<Agency> <AgencyName>Development Center</AgencyName> <aC_ProgramID>879</aC_ProgramID> <Site> <SiteName>Gompers-B25</SiteName> <Class> <ClassName>HS-103</ClassName> <DaysOpen> <Month>11</Month> <DateOpen>2014-11-25</DateOpen> </DaysOpen> <DaysOpen> <Month>11</Month> <DateOpen>2014-11-26</DateOpen> </DaysOpen> </Class> </Site> </Agency>
I do not have much experience with XML, so this may be a very simple solution.
Ultimately I would like to be able to list all <DateOpen> values relative to its parent as well as all values of <Class> relative to its parent, etc, etc. As you may have guessed Agency has a one-to-many relationship with Site, which has the same relationship with Class, same with DateOpen.
The purpose of getting the data to populate is this sequence is to use the "Count" function to provide a number of DateOpen rows by class and site and agency.
Thank you in advance for any direction.
|
For some time I noticed that C# code generation does not support the use of the sort function. I use the sort function for a lot of internal mapping processes to order by date and ID etc.
I though (wishful thinking) Altova would fix the limitation in one of the recent updates, but no luck.
Does anyone have a fix for this or an alternate approach to the sort function.
|
I know what you were referring to. When I choose complex-tree I have point to a file where the tree exists. The documentation says to use FlexText, but FlexText does not have much of its own documentation. I cannot figure out flextext to make the tree.
However, I just realized you can use a custom XML schema file as the structure. Perfect!
I have been using MapForce for two years and always get stuck here. Yes, MapForce documentation is generally very helpful.
Thanks for your help.
|
vlad wrote:#1 change your function to have a single output tree identical to the Rows subtree you want to create. This will allow you to connect only Rows with Rows with a copy-all connection
How do I change the output to a tree? Do I use FlexText? I can never seem to understand that tool. Please help.
vlad wrote:#3 use static-node-name function to retrieve the name of the source node. Important: your user-defined function must be Inlined, so that the actual node name can be retrieved.
Fantastic I did not know this function existed. It works exactly as I had hoped.
|
Description of Objectives
1) The source table contains several columns that need to transformed into multiple rows of less columns.
2) Each column record that is to be transformed contains multiple pieces of data that needs to be extracted to a few separate columns.
3) Store the source column name as rows in the target
Where I am so far
#2 is trivial; I created a function to handle this.
#1 Requires several duplicate rows and the above function. With each duplicate row I have to manually connect all of the same fields of the function except one of the inputs. This process is tedious and error prone as the number of columns (thus dupe rows needed) is greatly higher than the sample mapping below.
#3 No clue how to do this in MapForce. I am doing this manually by using constants, but it is not ideal.
For additional detail: below is a list of fields and definitions to help explain the transformation. I have also attached an image and a sample mfd.
Source: Example
ID
Bed1 (string; contains status of the bed)
Bed2 (string; contains status of the bed)
Bed3 (string; contains status of the bed)
Date
Target: Example
ID (same as source)
Bed_ID (string; column name of source. i.e. Bed1)
Available (bool; based on string contained in Bed_ID field. i.e. "unavailable" = false)
Occupied (bool; based on string contained in Bed_ID field.)
Gender (string; based on string contained in Bed_ID field.)
Date (same as source)
Thank you for any help!!
|
Oh wow, you are not kidding. After all this time, it was right in front of my face. I sure wish I would have figured this one out sooner.
Thanks again Vlad, hope life is treating you well.
|
I have a source table that contains about 70 columns that need to be transformed into a singular column on my temp target table. I am achieving this by using the duplicate input function, but I'm wondering if there is an easier/faster way. As you can see I have created a function that hold most of the data transformation but all of these connections have to be re-mapped with every duplicate input (70 total duplicate inputs)
Is their a way for me to copy all the connections from the previous duplicate input so I would only need to change the 1 column that needs to be normalized rather than mundane alternative. Or is there an easier way I am overlooking?
|
|
