Question

Dynamic attributes. Create attribute for every month for a year and then calculate values.

  • 18 February 2020
  • 4 replies
  • 16 views
K

Difficult to word this one.

 

I need to generate a table where attributes are generated dynamically e.g.

NameFeb-20Mar-20Apr-20

 

 

 

 

 

 

Where the attributes reflect every month from "now" for a set period e.g. a year.

I have created a workbench which generates the month-year as attribute values dynamically and then creating Attribute names from these values. However, I can't seem to find a way of exposing these attributes dynamically so that I can then assign values to them. (Currently I have to export them and then reimport them within the attribute exposer).

Is there anyway of doing this in a more streamlined fashion?

4 replies

ebygomm
Rank
Userlevel 1
Badge +10
Is there anyway of doing this in a more streamlined fashion?

Not really, if you are dynamically creating attributes then referencing them within the workspace the workspace is no longer dynamic and would need to be updated each time it's run.

It is however possible to set attributes without exposing them and then create a schema so the output can be written without exposing the attributes at all. If you could provide a bit more information about what you are trying to acheive someone may be able to advise further.

Example

Dynamic_table_example.fmw

K

Is it possible to use the bulkattributerenamer to rename the attributes based on a look-up?

 

E.g. Table 1 with generic attribute names

 

Attribute1Attribute2Attribute3Attribute4

 

 

 

 

 

E.g. Table 2 automatically created table (date values will update on each run)

Attribute NameAuto-generated dateAttribute1Feb-20Attribute2Mar-20Attribute3Apr-20

 

So I would like to use Table 2 as a look-up for the bulkattributerenamer. Renaming the Attributes in Table 1 with the Auto-generated date from Table 2 where the Attribute Name in Table 1 matches the Attribute Name value in Table 2.

A

Hi @kat18, would it be possible for you to share your workspace (.fmw)/screenshots of what you have achieved so far, along with some sample data, so we can take a closer look at it? Thanks!

B
Badge +3

The easiest way to work with the data is to not Cross tabulate at all.

Instead of having a table with Attributes of "Name", "Feb-20", "Mar-20" etc..... have a 3 attribute table like this:

Name, Month, Value

 

So to assign a value to a Name of say "Kat" and the Month of "Feb-20", then use something like an AttributeCreator that assigns a Conditional Value WHERE Name="Kat" AND Month = "Feb-20", or similary use the Month value inside a formula that takes this as an input to calculate the value for that Name, Month combination.

To assign values to all months at once then use something like StatisticsCalculator with GROUP BY set to "Name", "Month", or similarly use something like the InlineQuerier to do Avg, Min, Max, Count etc. with a SELECT Statement GROUP BY also set to "GROUP BY Name, Month"

It is far easier to perform calculations on data if it is kept Normalised like this instead of Cross-Tabbing it. I know that is how many people are used to viewing these tables inside Eg. MS Excel, but the trick to representing 2 dimensional tables inside data processing programs like databases and FME is to store the 2 dimensions as 2 different Fields/Attributes rather than make the 2nd Dimension attribute values as Field Names/Attribute Names. The golden rule is to save the Cross Tabulation to the end when outputting to the final table, which is usually only done for presentation/readability purposes. To expose the attributes on the final table is either an AttributeExposer or to write dynamically as @ebygomm has set out.

Reply


Community Stats

28,670
Posts
109,366
Replies
36,531
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.