table function for exchanging attributes & numbers

Table-valued functions in finaquant .net libraries

table function for exchanging attributes & numbers

Postby lotus85 » 01 Nov 2013, 20:24

Hi,
is there a table function for replacing each attribute value with a separate number, or vice verca?
lotus85
 
Posts: 6
Joined: 24 Sep 2013, 19:59

Re: table function for exchanging attributes & numbers

Postby finaquant » 02 Nov 2013, 10:48

Can you please explain what you mean by "replacing attribute value with a number?"
User avatar
finaquant
 
Posts: 61
Joined: 16 Sep 2013, 18:37

Re: table function for exchanging attributes & numbers

Postby lotus85 » 15 Nov 2013, 21:06

sorry for the late reply.

assume, you have products with different colors, each color may have a different price. so you may have a table like this:

product - color - price
pencil - red - 12.50
pencil - blue - 15.00

price is the key figure here, product and color are attributes. what if I want to introduce a separate key figure for each color, and get rid of the color attribute, like this:

product price_red price_blue ...
pencil - 12.50 - 15.00

is there a table function for this transformation?

thanks
lotus85
 
Posts: 6
Joined: 24 Sep 2013, 19:59

Separate key figure for each attribute value

Postby finaquant » 26 Nov 2013, 07:40

There is not yet a single Table Function for replacing attribute values with key figures, or vice versa, in our .NET libraries finaquant® protos or finaquant® calcs.

However, you can easily write your own custom table function for this purpose using table functions like Subtable Transformer and Row Transformer (row-by-row processing for applying a user-defined formula on data table).

Based on your example:

product - color - price
pencil - red - 12.50
pencil - blue - 15.00

A) Converting attribute values to separate key figures (a separate key figure for each attribute value like red, blue, ...)

Steps:
1) Get all distinct attribute values in the table (red, blue, ..)
2) Define a separate key figure in MetaData for each distinct attribute value like price_red, price_blue, ..
3) Using Subtable Transformer pass subtables with fields color and price (relevant attribute and key figure) to your custom table function which returns a key-figure-only subtable with fields like price_red and price_blue

.. to obtain a table like the following one:
product - price_red - price_blue
pencil - 12.50 - 15.00

B) Converting separate key figures back to a single key figure with different attribute values for each key figure

Steps:
1) Define attribute color and (general) key figure price in MetaData unless they were already defined
2) Using Subtable Transformer pass subtables with all key figures (price_red, price_blue, ..) to your custom table function, which returns subtables with fields color and price
User avatar
finaquant
 
Posts: 61
Joined: 16 Sep 2013, 18:37

C# code: Method to convert attribute values to key figures

Postby finaquant » 01 Dec 2013, 21:14

You can find below an example code for a custom (user-defined) table function named ConvertAttributeValuesToKeyFigures which converts the values of a selected attribute of an input table to an output table with separate key figures for each attribute value.

Note that the custom methods below are written quickly for demonstration purposes without elaborate tests or error checks.

We can test the C# method ConvertAttributeValuesToKeyFigures as follows:
Code: [Select all] [Expand/Collapse] [Download] (Untitled.txt)
  1. // define metadata
  2. MetaData md = MetaData.CreateEmptyMetaData();
  3. MetaData.AddNewField(md, "category", FieldType.TextAttribute);
  4. MetaData.AddNewField(md, "product", FieldType.TextAttribute);
  5. MetaData.AddNewField(md, "color", FieldType.TextAttribute);
  6. MetaData.AddNewField(md, "costs", FieldType.KeyFigure);
  7. MetaData.AddNewField(md, "price", FieldType.KeyFigure);
  8.  
  9. // define table structure for PriceTable
  10. var CostTableFields = TableFields.CreateEmptyTableFields(md);
  11. TableFields.AddNewField(CostTableFields, "category");
  12. TableFields.AddNewField(CostTableFields, "product");
  13. TableFields.AddNewField(CostTableFields, "color");
  14. TableFields.AddNewField(CostTableFields, "costs");
  15. TableFields.AddNewField(CostTableFields, "price");
  16.  
  17. // create PriceTable with elements
  18. MatrixTable PriceTable = MatrixTable.CreateTableWithElements_A(CostTableFields,
  19.     "Sports", "Car", "red", 100.0, 120.0,
  20.     "Sports", "Car", "blue", 120.0, 150.0,
  21.     "Sports", "Car", "green", 150.0, 180.0,
  22.     "Sports", "Bicycle", "red", 10.0, 12.0,
  23.     "Sports", "Bicycle", "blue", 12.0, 15.0,
  24.     "Sports", "Bicycle", "green", 15.0, 18.0,
  25.    
  26.     "Economy", "Car", "red", 800.0, 100.0,
  27.     "Economy", "Car", "blue", 100.0, 120.0,
  28.     "Economy", "Car", "green", 120.0, 150.0,
  29.     "Economy", "Bicycle", "red", 8.0, 10.0,
  30.     "Economy", "Bicycle", "blue", 10.0, 12.0,
  31.     "Economy", "Bicycle", "green", 12.0, 15.0
  32.     );
  33. // view PriceTable
  34. MatrixTable.View_MatrixTable(PriceTable, "PriceTable original");
  35.  
  36. // transform attribute values to key figures
  37. MatrixTable ConvPriceTable = UserFunctions.ConvertAttributeValuesToKeyFigures(PriceTable, "color", "price");
  38.  
  39. // view converted PriceTable
  40. MatrixTable.View_MatrixTable(ConvPriceTable, "Converted PriceTable (color values --> price_[color])");
GeSHi ©

Input & Output tables:
Image

Method (C# code) that convert attribute values of a table to separate key figures
ConvertAttributeValuesToKeyFigures is the main method including a Subtable Transformer which calls the second method named AttributeValuesToKeyFigures.

Code: [Select all] [Expand/Collapse] [Download] (Untitled.txt)
  1. /// <summary>
  2. /// Convert all attribute values to separate key figures, like price_red, price_blue..
  3. /// for given attribute color and key figure price.
  4. /// </summary>
  5. /// <param name="InputTbl">Input Table</param>
  6. /// <param name="Attribute">Name of the the selected text attribute of input table</param>
  7. /// <param name="KeyFigure">Name of the selected key figure of input table</param>
  8. /// <returns>Output table with separate key figures for each attribute value</returns>
  9. public static MatrixTable ConvertAttributeValuesToKeyFigures(MatrixTable InputTbl, string Attribute, string KeyFigure)
  10. {
  11.     try
  12.     {
  13.         // CHECK ALL INPUTS
  14.         if (InputTbl == null || InputTbl.IsEmpty || InputTbl.RowCount == 0)
  15.             throw new Exception("ConvertAttributeValuesToKeyFigures: Null-valued or empty input table!\n");
  16.    
  17.         if (Attribute == null || Attribute == "")
  18.             throw new Exception("ConvertAttributeValuesToKeyFigures: Null-valued or empty string for Attribute!\n");
  19.  
  20.         if (Attribute == null || KeyFigure == "")
  21.             throw new Exception("ConvertAttributeValuesToKeyFigures: Null-valued or empty string for KeyFigure!\n");
  22.  
  23.         // check if Attribute is contained by input table
  24.         string AttributeLow = Attribute.ToLower();
  25.         if (!TextVector.IfValueFoundInSet(AttributeLow, InputTbl.TextAttributeFields))
  26.             throw new Exception("ConvertAttributeValuesToKeyFigures: " + AttributeLow + " must be a text attribute of input table!\n");
  27.  
  28.         // check if KeyFigure is contained by input table
  29.         string KeyFigLow = KeyFigure.ToLower();
  30.         if (!TextVector.IfValueFoundInSet(KeyFigLow, InputTbl.KeyFigureFields))
  31.             throw new Exception("ConvertAttributeValuesToKeyFigures: " + KeyFigLow + " must be a key figure of input table!\n");
  32.  
  33.  
  34.         // get MetaData of input table
  35.         MetaData md = InputTbl.metaData;
  36.  
  37.         // get all distinct attribute values
  38.         TextVector AttributeValues = InputTbl.GetColumnTextAttribute(AttributeLow).Unique();
  39.  
  40.         // add all new key figures to MetaData
  41.         string NewKeyFigure;
  42.         string KeyFigPrefix = KeyFigure.ToLower() + "_";
  43.  
  44.         // fields of OutSubTable (passed as parameter to Subtable Transformer)
  45.         TableFields OutSubTableFields = TableFields.CreateEmptyTableFields(md);
  46.  
  47.         foreach (var attrib in AttributeValues.toArray)
  48.         {
  49.             NewKeyFigure = KeyFigPrefix + attrib.ToLower();
  50.  
  51.             // check if a field with the same name is already defined in MetaData
  52.             if (MetaData.GetFieldType(md, NewKeyFigure) != FieldType.Undefined
  53.                     && MetaData.GetFieldType(md, NewKeyFigure) != FieldType.KeyFigure)
  54.                 throw new Exception("ConvertAttributeValuesToKeyFigures: There is an attribue field named " + NewKeyFigure + " in MetaData!");
  55.  
  56.             // add key figure to MetaData if it doesn't already exist
  57.             if (MetaData.GetFieldType(md, NewKeyFigure) == FieldType.Undefined)
  58.                 MetaData.AddNewField(md, NewKeyFigure, FieldType.KeyFigure);
  59.  
  60.             // add key figure to OutSubTableFields
  61.             TableFields.AddNewField(OutSubTableFields, NewKeyFigure);
  62.         }
  63.  
  64.         // fields of SubTable
  65.         TableFields SubTableFields = TableFields.CreateEmptyTableFields(md);
  66.         TableFields.AddNewField(SubTableFields, AttributeLow);
  67.         TableFields.AddNewField(SubTableFields, KeyFigLow);
  68.  
  69.         // call SubTable Transformer for converting attribute values to separate key figures
  70.         MatrixTable ResultTbl = MatrixTable.TransformSubTables(InputTbl, SubTableFields,
  71.             UserFunctions.AttributeValuesToKeyFigures, OutSubTableFields);
  72.  
  73.         // return resultant table
  74.         return ResultTbl;
  75.     }
  76.     catch (Exception ex)
  77.     {
  78.         throw new Exception("ConvertAttributeValuesToKeyFigures: " + ex.Message);
  79.     }
  80. }
  81.  
  82. /// <summary>
  83. /// User-defined table function called by Subtable Transformer.
  84. /// Convert a single-TextAttribute and single-KeyFigure SubTable to an output tables with key figures only;
  85. /// like (color, price) --> (price_red, price_blue, price_black, ...)
  86. /// </summary>
  87. /// <param name="SubTable">Input table with a single text attribute and a single key figure</param>
  88. /// <param name="Parameters">[0]: TableFields with the key figures to be returned</param>
  89. /// <returns>Output table with key figures only</returns>
  90. private static MatrixTable AttributeValuesToKeyFigures(MatrixTable SubTable, params Object[] Parameters)
  91. {
  92.     try
  93.     {
  94.         // PARAMETER CHECKS
  95.         // table function for demonstration purposes, no elaborate parameter checks;
  96.         // assumes parameters are entered correctly.
  97.         TableFields OutputTblFields = (TableFields) Parameters[0];
  98.  
  99.         // get attribute and key figure names
  100.         string TextAttrib = SubTable.TextAttributeFields[0];
  101.         string KeyFig = SubTable.KeyFigureFields[0];
  102.  
  103.         // create a new table row with key figures only
  104.         TableRow OutputTblRow = TableRow.CreateDefaultTableRow(OutputTblFields);
  105.  
  106.         // assign values to each key figure
  107.         TableRow InputTblRow;
  108.         string AttribValue;
  109.         string NewKeyFig;
  110.  
  111.         for (int i = 0; i < SubTable.RowCount; i++)
  112.         {
  113.             InputTblRow = SubTable.GetTableRow(i);
  114.             AttribValue = (string) InputTblRow.GetFieldValue(TextAttrib);
  115.             NewKeyFig = KeyFig + "_" + AttribValue.ToLower();
  116.  
  117.             TableRow.SetKeyFigureValue(OutputTblRow, NewKeyFig, (double)InputTblRow.GetFieldValue(KeyFig));
  118.         }
  119.  
  120.         // Append row to an empty table
  121.         MatrixTable OutTbl = MatrixTable.CreateEmptyTable(OutputTblFields);
  122.         return MatrixTable.AddRowToTable(OutTbl, OutputTblRow);
  123.     }
  124.     catch (Exception ex)
  125.     {
  126.         throw new Exception("AttributeValuesToKeyFigures: " + ex.Message);
  127.     }
  128. }
GeSHi ©
User avatar
finaquant
 
Posts: 61
Joined: 16 Sep 2013, 18:37


Return to Table Functions

Who is online

Users browsing this forum: No registered users and 1 guest

cron