{ "version": "6.1.0", "className": "datameer.dap.common.entity.WorkbookConfigurationImpl", "file": { "uuid": "bd0189e8-ca75-4bef-b1fe-98cdc65c404c", "path": "/Users/admin/Applications/PercentRank/_01_PercentRank.wbk", "description": "", "name": "_01_PercentRank" }, "pullType": "MANUALLY", "minKeepCount": 1, "properties": {}, "hadoopProperties": "", "notificationAddresses": "", "notificationSuccessAddresses": "", "sheets": [ { "name": "Input_data", "sheetId": "84ea458b-0dcd-46f5-a5dc-d0ddbc127376", "sheetType": "das.internal.FormulaSheetType", "keep": true, "formulas": [ { "columnName": "Index", "columnId": "0", "columnIndex": 0, "formulaString": "\u003dEXPLODE_RANGE(1;30)" }, { "columnName": "Data", "columnId": "1", "columnIndex": 1, "formulaString": "\u003dROUND(RANDBETWEEN(1;30))" } ], "columnStyles": [ { "columnId": "0", "name": "Index", "width": 100, "thousandSeparator": true }, { "columnId": "1", "name": "Data", "width": 100, "thousandSeparator": true } ], "nextColumnId": 2 }, { "name": "Grouping", "sheetId": "2c0ec07e-c6ae-42ed-830a-b8ba0e0f925f", "sheetType": "das.internal.FormulaSheetType", "keep": true, "formulas": [ { "columnName": "GroupKey", "columnId": "1", "columnIndex": 0, "formulaString": "\u003d1" }, { "columnName": "Data", "columnId": "0", "columnIndex": 1, "formulaString": "\u003d#Input_data!Data" } ], "columnStyles": [ { "columnId": "0", "name": "Data", "width": 100, "thousandSeparator": true }, { "columnId": "1", "name": "GroupKey", "width": 100, "thousandSeparator": true } ], "nextColumnId": 2 }, { "name": "Counting", "sheetId": "ab9880e0-177d-476f-b239-38fa3d3f1bb0", "sheetType": "das.internal.FormulaSheetType", "keep": true, "formulas": [ { "columnName": "GroupKey", "columnId": "0", "columnIndex": 0, "formulaString": "\u003dGROUPBY(#Grouping!GroupKey)" }, { "columnName": "Data", "columnId": "1", "columnIndex": 1, "formulaString": "\u003dGROUPCOUNT()" } ], "columnStyles": [ { "columnId": "0", "name": "GroupKey", "width": 100, "thousandSeparator": true }, { "columnId": "1", "name": "Data", "width": 100, "thousandSeparator": true } ], "nextColumnId": 2 }, { "name": "Sorting", "sheetId": "ade2e303-4692-46da-9c58-be0e83970d29", "sheetType": "das.internal.FormulaSheetType", "keep": true, "formulas": [ { "columnName": "GroupKey", "columnId": "0", "columnIndex": 0, "formulaString": "\u003dGROUPBY(#Grouping!GroupKey)" }, { "columnName": "Data", "columnId": "1", "columnIndex": 1, "formulaString": "\u003dGROUP_SORT_DESC(#Grouping!Data)" }, { "columnName": "Rank", "columnId": "2", "columnIndex": 2, "formulaString": "\u003dGROUPROWNUMBER()" } ], "columnStyles": [ { "columnId": "0", "name": "GroupKey", "width": 100, "thousandSeparator": true }, { "columnId": "1", "name": "Data", "width": 100, "thousandSeparator": true }, { "columnId": "2", "name": "Rank", "width": 100, "thousandSeparator": true } ], "nextColumnId": 3 }, { "name": "Duplicate_reRanking", "sheetId": "e68969ec-e17e-4920-987b-5ebf8b9d4dbb", "sheetType": "das.internal.FormulaSheetType", "keep": true, "formulas": [ { "columnName": "Data", "columnId": "0", "columnIndex": 0, "formulaString": "\u003dGROUPBY(#Sorting!Data)" }, { "columnName": "Rank", "columnId": "1", "columnIndex": 1, "formulaString": "\u003dGROUPCONCAT(#Sorting!Rank)" }, { "columnName": "Sorted_Rank", "columnId": "2", "columnIndex": 2, "formulaString": "\u003dSORT(#Rank)" }, { "columnName": "Cardinality", "columnId": "4", "columnIndex": 3, "formulaString": "\u003dSIZE(#Rank)" }, { "columnName": "LowestRank", "columnId": "3", "columnIndex": 4, "formulaString": "\u003dLISTELEMENT(#Sorted_Rank;0)" } ], "columnStyles": [ { "columnId": "0", "name": "Data", "width": 100, "thousandSeparator": true }, { "columnId": "1", "name": "Rank", "width": 100, "thousandSeparator": true }, { "columnId": "2", "name": "Sorted_Rank", "width": 100, "thousandSeparator": true }, { "columnId": "3", "name": "LowestRank", "width": 100, "thousandSeparator": true }, { "columnId": "4", "name": "Cardinality", "width": 100, "thousandSeparator": true } ], "nextColumnId": 5 }, { "name": "RankAdjusted", "sheetId": "e82a5126-8f82-4545-9f17-79f9aac832b9", "sheetType": "das.internal.FormulaSheetType", "keep": true, "formulas": [ { "columnName": "GroupKey", "columnId": "4", "columnIndex": 0, "formulaString": "\u003dGROUPBY(1)" }, { "columnName": "Expanded", "columnId": "1", "columnIndex": 1, "formulaString": "\u003dEXPLODE_RANGE(1;#Duplicate_reRanking!Cardinality)" }, { "columnName": "Data", "columnId": "0", "columnIndex": 2, "formulaString": "\u003dGROUP_SORT_DESC(#Duplicate_reRanking!Data)" }, { "columnName": "Rank", "columnId": "3", "columnIndex": 3, "formulaString": "\u003d#Duplicate_reRanking!LowestRank" } ], "columnStyles": [ { "columnId": "0", "name": "Data", "width": 100, "thousandSeparator": true }, { "columnId": "1", "name": "Expanded", "width": 100, "thousandSeparator": true }, { "columnId": "3", "name": "Rank", "width": 100, "thousandSeparator": true }, { "columnId": "4", "name": "GroupKey", "width": 100, "thousandSeparator": true } ], "nextColumnId": 5 }, { "name": "Joined", "sheetId": "c2e38520-e2a9-431b-976a-9ee1edb5fcc9", "sheetType": "das.internal.JoinedSheetType", "keep": true, "sheetDefinition": { "joinCategory": "MULTI_JOIN", "joinPairs": [ { "joinType": "INNER", "sheet1": "Counting", "sheet2": "RankAdjusted", "joinColumns1": [ "GroupKey" ], "joinColumns2": [ "GroupKey" ] }, { "joinType": "INNER", "sheet1": "RankAdjusted", "sheet2": "Duplicate_reRanking", "joinColumns1": [ "Data" ], "joinColumns2": [ "Data" ] } ], "sheetToIncludeColumns": { "Counting": [ "GroupKey", "Data" ], "RankAdjusted": [ "Data", "Rank" ], "Duplicate_reRanking": [ "Cardinality" ] } }, "columnStyles": [ { "columnId": "18345aa0c7d33450d8931f1e3e3047b5", "name": "GroupKey", "width": 100, "thousandSeparator": true }, { "columnId": "610999a36a6eb84366c371854f105056", "name": "Count", "width": 100, "thousandSeparator": true }, { "columnId": "e3d77694f66579afcab762a77f013b65", "name": "Data", "width": 100, "thousandSeparator": true }, { "columnId": "45209e0b33ad0f4808116835f116d9e3", "name": "Rank", "width": 100, "thousandSeparator": true }, { "columnId": "fff9c4773bc16ccd90c6714545195423", "name": "Cardinality", "width": 100, "thousandSeparator": true } ] }, { "name": "PercentRank", "sheetId": "e6674b68-2436-4f75-acf1-f3895eaefed5", "sheetType": "das.internal.FormulaSheetType", "keep": true, "formulas": [ { "columnName": "GroupKey", "columnId": "0", "columnIndex": 0, "formulaString": "\u003dGROUPBY(#Joined!GroupKey)" }, { "columnName": "Data", "columnId": "2", "columnIndex": 1, "formulaString": "\u003dGROUP_SORT_DESC(#Joined!Data)" }, { "columnName": "Inclusive", "columnId": "5", "columnIndex": 2, "formulaString": "\u003dDIV(SUBTRACT(#Joined!Count;#Joined!Cardinality;SUBTRACT(#Joined!Rank;1));SUBTRACT(#Joined!Count;1))" }, { "columnName": "Exclusive", "columnId": "6", "columnIndex": 3, "formulaString": "\u003dDIV(SUM(SUBTRACT(#Joined!Count;#Joined!Cardinality;SUBTRACT(#Joined!Rank;1));1);SUM(#Joined!Count;1))" } ], "columnStyles": [ { "columnId": "0", "name": "GroupKey", "width": 100, "thousandSeparator": true }, { "columnId": "2", "name": "Data", "width": 100, "thousandSeparator": true }, { "columnId": "5", "name": "Inclusive", "width": 100, "thousandSeparator": true }, { "columnId": "6", "name": "Exclusive", "width": 100, "thousandSeparator": true } ], "nextColumnId": 7 }, { "name": "Interpolation_part1", "sheetId": "9cf7b68f-c977-4105-a5e1-254b6e07e928", "sheetType": "das.internal.FormulaSheetType", "keep": true, "filterSourceSheet": "Interpolation_part1", "filterTargetSheet": "Interpolation_part1", "connector": "AND", "filterArguments": [ { "column": "Difference", "filterExpression": "NOT_EQUALS", "value": "0", "valueStatic": true, "type": "CONSTANT" } ], "formulas": [ { "columnName": "GroupKey", "columnId": "0", "columnIndex": 0, "formulaString": "\u003dGROUPBY(#PercentRank!GroupKey)" }, { "columnName": "Data", "columnId": "1", "columnIndex": 1, "formulaString": "\u003d#PercentRank!Data" }, { "columnName": "Previous_Data", "columnId": "4", "columnIndex": 2, "formulaString": "\u003dGROUP_PREVIOUS(#PercentRank!Data)" }, { "columnName": "Difference", "columnId": "5", "columnIndex": 3, "formulaString": "\u003dIF(ISBLANK(#Interpolation_part1!Previous_Data);0;SUBTRACT(#Previous_Data;#Data))" }, { "columnName": "Inclusive", "columnId": "6", "columnIndex": 4, "formulaString": "\u003d#PercentRank!Inclusive" }, { "columnName": "Exclusive", "columnId": "7", "columnIndex": 5, "formulaString": "\u003d#PercentRank!Exclusive" }, { "columnName": "Previous_Inclusive", "columnId": "8", "columnIndex": 6, "formulaString": "\u003dGROUP_PREVIOUS(#PercentRank!Inclusive)" }, { "columnName": "Previous_Exclusive", "columnId": "9", "columnIndex": 7, "formulaString": "\u003dGROUP_PREVIOUS(#PercentRank!Exclusive)" }, { "columnName": "Input", "columnId": "11", "columnIndex": 8, "formulaString": "\u003d25" }, { "columnName": "Within_interval", "columnId": "10", "columnIndex": 9, "formulaString": "\u003d(#Previous_Data\u003e\u003d#Input)\u0026\u0026(#Data\u003c\u003d#Input)" } ], "columnStyles": [ { "columnId": "0", "name": "GroupKey", "width": 100, "thousandSeparator": true }, { "columnId": "1", "name": "Data", "width": 100, "thousandSeparator": true }, { "columnId": "4", "name": "Previous_Data", "width": 100, "thousandSeparator": true }, { "columnId": "5", "name": "Difference", "width": 100, "thousandSeparator": true }, { "columnId": "6", "name": "Inclusive", "width": 100, "thousandSeparator": true }, { "columnId": "7", "name": "Exclusive", "width": 100, "thousandSeparator": true }, { "columnId": "8", "name": "Previous_Inclusive", "width": 100, "thousandSeparator": true }, { "columnId": "9", "name": "Previous_Exclusive", "width": 100, "thousandSeparator": true }, { "columnId": "10", "name": "Within_interval", "width": 100, "thousandSeparator": true }, { "columnId": "11", "name": "Input", "width": 100, "thousandSeparator": true } ], "nextColumnId": 12 }, { "name": "Interpolation_part2", "sheetId": "4d15fa33-3060-4fab-b411-b8405ba22757", "sheetType": "das.internal.FormulaSheetType", "keep": true, "filterSourceSheet": "Interpolation_part2", "filterTargetSheet": "Interpolation_part2", "connector": "AND", "filterArguments": [ { "column": "Within_interval", "filterExpression": "TRUE", "value": "null", "valueStatic": true, "type": "CONSTANT" } ], "formulas": [ { "columnName": "GroupKey", "columnId": "0", "columnIndex": 0, "formulaString": "\u003d#Interpolation_part1!GroupKey" }, { "columnName": "Upper_data", "columnId": "2", "columnIndex": 1, "formulaString": "\u003d#Interpolation_part1!Previous_Data" }, { "columnName": "Lower_data", "columnId": "1", "columnIndex": 2, "formulaString": "\u003d#Interpolation_part1!Data" }, { "columnName": "Input", "columnId": "4", "columnIndex": 3, "formulaString": "\u003d25" }, { "columnName": "Proportion", "columnId": "9", "columnIndex": 4, "formulaString": "\u003dDIV(SUBTRACT(#Input;#Lower_data);#Interpolation_part1!Difference)" }, { "columnName": "Within_interval", "columnId": "7", "columnIndex": 5, "formulaString": "\u003d(#Upper_data\u003e\u003d#Input)\u0026\u0026(#Lower_data\u003c\u003d#Input)" }, { "columnName": "Inclusive_PR", "columnId": "18", "columnIndex": 6, "formulaString": "\u003dSUM(PRODUCT(SUBTRACT(#Interpolation_part1!Previous_Inclusive;#Interpolation_part1!Inclusive);DIV(SUBTRACT(#Input;#Lower_data);#Interpolation_part1!Difference));#Interpolation_part1!Inclusive)" }, { "columnName": "Exclusive_PR", "columnId": "19", "columnIndex": 7, "formulaString": "\u003dSUM(#Interpolation_part1!Exclusive;PRODUCT(SUBTRACT(#Interpolation_part1!Previous_Exclusive;#Interpolation_part1!Exclusive);DIV(SUBTRACT(#Input;#Lower_data);#Interpolation_part1!Difference)))" } ], "columnStyles": [ { "columnId": "0", "name": "GroupKey", "width": 100, "thousandSeparator": true }, { "columnId": "1", "name": "Lower_data", "width": 100, "thousandSeparator": true }, { "columnId": "2", "name": "Upper_data", "width": 100, "thousandSeparator": true }, { "columnId": "4", "name": "Input", "width": 100, "thousandSeparator": true }, { "columnId": "7", "name": "Within_interval", "width": 100, "thousandSeparator": true }, { "columnId": "9", "name": "Proportion", "width": 100, "thousandSeparator": true }, { "columnId": "18", "name": "Inclusive_PR", "width": 100, "thousandSeparator": true }, { "columnId": "19", "name": "Exclusive_PR", "width": 100, "thousandSeparator": true } ], "nextColumnId": 20 } ], "advancedSchedulingInUse": true, "errorHandlingMode": "IGNORE" }