diff options
Diffstat (limited to 'calculate_bi_marketing_md.ktr')
| -rw-r--r-- | calculate_bi_marketing_md.ktr | 1763 |
1 files changed, 871 insertions, 892 deletions
diff --git a/calculate_bi_marketing_md.ktr b/calculate_bi_marketing_md.ktr index 83e522e..2c9fd24 100644 --- a/calculate_bi_marketing_md.ktr +++ b/calculate_bi_marketing_md.ktr @@ -430,7 +430,7 @@ <created_date>2025/05/13 13:45:32.351</created_date> <modified_user>-</modified_user> <modified_date>2025/05/13 13:45:32.351</modified_date> - <key_for_session_key/> + <key_for_session_key>H4sIAAAAAAAAAAMAAAAAAAAAAAA=</key_for_session_key> <is_key_private>N</is_key_private> </info> <notepads> @@ -655,8 +655,8 @@ </hop> </order> <step> - <name>parent category</name> - <type>TableInput</type> + <name>Calculator</name> + <type>Calculator</type> <description/> <distribute>Y</distribute> <custom_distribution/> @@ -665,82 +665,225 @@ <method>none</method> <schema_name/> </partitioning> - <connection>erp indoteknik production</connection> - <sql>select id, category, now()::timestamp-'7 hours'::interval as history_date -from( - select ppc.id, name as category - from product_public_category ppc - where ppc.id in( - 2040, - 2097, - 2161, - 2222, - 2246, - 2273, - 2315, - 2354, - 2394, - 2420, - 2477, - 2445 - ) - union - select 999999, 'Others' -)a order by id</sql> - <limit>0</limit> - <lookup/> - <execute_each_row>N</execute_each_row> - <variables_active>N</variables_active> - <lazy_conversion_active>N</lazy_conversion_active> - <cached_row_meta_active>N</cached_row_meta_active> - <row-meta> - <value-meta> - <type>Integer</type> - <storagetype>normal</storagetype> + <failIfNoFile>Y</failIfNoFile> + <calculation> + <field_name>diff_ly</field_name> + <calc_type>SUBTRACT</calc_type> + <field_a>this_year</field_a> + <field_b>last_year</field_b> + <field_c/> + <value_type>Number</value_type> + <value_length>-1</value_length> + <value_precision>-1</value_precision> + <remove>N</remove> + <conversion_mask/> + <decimal_symbol/> + <grouping_symbol/> + <currency_symbol/> + </calculation> + <calculation> + <field_name>growth_value</field_name> + <calc_type>DIVIDE</calc_type> + <field_a>diff_ly</field_a> + <field_b>last_year</field_b> + <field_c/> + <value_type>Number</value_type> + <value_length>-1</value_length> + <value_precision>-1</value_precision> + <remove>N</remove> + <conversion_mask/> + <decimal_symbol/> + <grouping_symbol/> + <currency_symbol/> + </calculation> + <calculation> + <field_name>diff_target</field_name> + <calc_type>SUBTRACT</calc_type> + <field_a>this_year</field_a> + <field_b>target</field_b> + <field_c/> + <value_type>Number</value_type> + <value_length>-1</value_length> + <value_precision>-1</value_precision> + <remove>N</remove> + <conversion_mask/> + <decimal_symbol/> + <grouping_symbol/> + <currency_symbol/> + </calculation> + <calculation> + <field_name>achv_target</field_name> + <calc_type>DIVIDE</calc_type> + <field_a>diff_target</field_a> + <field_b>target</field_b> + <field_c/> + <value_type>Number</value_type> + <value_length>-1</value_length> + <value_precision>-1</value_precision> + <remove>N</remove> + <conversion_mask/> + <decimal_symbol/> + <grouping_symbol/> + <currency_symbol/> + </calculation> + <calculation> + <field_name>diff_trx</field_name> + <calc_type>SUBTRACT</calc_type> + <field_a>trx_this_year</field_a> + <field_b>trx_last_year</field_b> + <field_c/> + <value_type>Number</value_type> + <value_length>-1</value_length> + <value_precision>-1</value_precision> + <remove>N</remove> + <conversion_mask/> + <decimal_symbol/> + <grouping_symbol/> + <currency_symbol/> + </calculation> + <calculation> + <field_name>growth_trx</field_name> + <calc_type>DIVIDE</calc_type> + <field_a>diff_trx</field_a> + <field_b>trx_last_year</field_b> + <field_c/> + <value_type>Number</value_type> + <value_length>-1</value_length> + <value_precision>-1</value_precision> + <remove>N</remove> + <conversion_mask/> + <decimal_symbol/> + <grouping_symbol/> + <currency_symbol/> + </calculation> + <calculation> + <field_name>diff_ly_full</field_name> + <calc_type>SUBTRACT</calc_type> + <field_a>this_year</field_a> + <field_b>last_year_full</field_b> + <field_c/> + <value_type>Number</value_type> + <value_length>-1</value_length> + <value_precision>-1</value_precision> + <remove>N</remove> + <conversion_mask/> + <decimal_symbol/> + <grouping_symbol/> + <currency_symbol/> + </calculation> + <calculation> + <field_name>growth_value_full</field_name> + <calc_type>DIVIDE</calc_type> + <field_a>diff_ly_full</field_a> + <field_b>last_year_full</field_b> + <field_c/> + <value_type>Number</value_type> + <value_length>-1</value_length> + <value_precision>-1</value_precision> + <remove>N</remove> + <conversion_mask/> + <decimal_symbol/> + <grouping_symbol/> + <currency_symbol/> + </calculation> + <attributes/> + <cluster_schema/> + <remotesteps> + <input> + </input> + <output> + </output> + </remotesteps> + <GUI> + <xloc>864</xloc> + <yloc>384</yloc> + <draw>Y</draw> + </GUI> + </step> + <step> + <name>Insert / update</name> + <type>InsertUpdate</type> + <description/> + <distribute>Y</distribute> + <custom_distribution/> + <copies>1</copies> + <partitioning> + <method>none</method> + <schema_name/> + </partitioning> + <connection>dw bi indoteknik</connection> + <commit>100</commit> + <update_bypassed>N</update_bypassed> + <lookup> + <schema>public</schema> + <table>t_omset_by_parent_category</table> + <key> <name>id</name> - <length>9</length> - <precision>0</precision> - <origin>parent category</origin> - <comments>id</comments> - <conversion_Mask>####0;-####0</conversion_Mask> - <decimal_symbol>.</decimal_symbol> - <grouping_symbol>,</grouping_symbol> - <currency_symbol/> - <trim_type>none</trim_type> - <case_insensitive>N</case_insensitive> - <collator_disabled>Y</collator_disabled> - <collator_strength>0</collator_strength> - <sort_descending>N</sort_descending> - <output_padding>N</output_padding> - <date_format_lenient>N</date_format_lenient> - <date_format_locale>en_US</date_format_locale> - <date_format_timezone>Asia/Bangkok</date_format_timezone> - <lenient_string_to_number>N</lenient_string_to_number> - </value-meta> - <value-meta> - <type>String</type> - <storagetype>normal</storagetype> + <field>id</field> + <condition>=</condition> + <name2/> + </key> + <value> + <name>id</name> + <rename>id</rename> + <update>N</update> + </value> + <value> <name>category</name> - <length>2147483647</length> - <precision>-1</precision> - <origin>parent category</origin> - <comments>category</comments> - <conversion_Mask/> - <decimal_symbol>.</decimal_symbol> - <grouping_symbol>,</grouping_symbol> - <currency_symbol/> - <trim_type>none</trim_type> - <case_insensitive>N</case_insensitive> - <collator_disabled>Y</collator_disabled> - <collator_strength>0</collator_strength> - <sort_descending>N</sort_descending> - <output_padding>N</output_padding> - <date_format_lenient>N</date_format_lenient> - <date_format_locale>en_US</date_format_locale> - <date_format_timezone>Asia/Bangkok</date_format_timezone> - <lenient_string_to_number>N</lenient_string_to_number> - </value-meta> - </row-meta> + <rename>category</rename> + <update>Y</update> + </value> + <value> + <name>history_date</name> + <rename>history_date</rename> + <update>Y</update> + </value> + <value> + <name>last_year</name> + <rename>last_year</rename> + <update>Y</update> + </value> + <value> + <name>this_year</name> + <rename>this_year</rename> + <update>Y</update> + </value> + <value> + <name>target</name> + <rename>target</rename> + <update>Y</update> + </value> + <value> + <name>trx_last_year</name> + <rename>trx_last_year</rename> + <update>Y</update> + </value> + <value> + <name>trx_this_year</name> + <rename>trx_this_year</rename> + <update>Y</update> + </value> + <value> + <name>growth_value</name> + <rename>growth_value</rename> + <update>Y</update> + </value> + <value> + <name>achv_target</name> + <rename>achv_target</rename> + <update>Y</update> + </value> + <value> + <name>growth_trx</name> + <rename>growth_trx</rename> + <update>Y</update> + </value> + <value> + <name>growth_value_full</name> + <rename>growth_value_full</rename> + <update>Y</update> + </value> + </lookup> <attributes/> <cluster_schema/> <remotesteps> @@ -750,14 +893,48 @@ from( </output> </remotesteps> <GUI> - <xloc>160</xloc> + <xloc>624</xloc> + <yloc>384</yloc> + <draw>Y</draw> + </GUI> + </step> + <step> + <name>Merge join</name> + <type>MergeJoin</type> + <description/> + <distribute>Y</distribute> + <custom_distribution/> + <copies>1</copies> + <partitioning> + <method>none</method> + <schema_name/> + </partitioning> + <join_type>LEFT OUTER</join_type> + <step1>Sort rows</step1> + <step2>Sort rows 2</step2> + <keys_1> + <key>id</key> + </keys_1> + <keys_2> + <key>product_category_id</key> + </keys_2> + <attributes/> + <cluster_schema/> + <remotesteps> + <input> + </input> + <output> + </output> + </remotesteps> + <GUI> + <xloc>352</xloc> <yloc>288</yloc> <draw>Y</draw> </GUI> </step> <step> - <name>last year</name> - <type>TableInput</type> + <name>Merge join 2</name> + <type>MergeJoin</type> <description/> <distribute>Y</distribute> <custom_distribution/> @@ -766,70 +943,206 @@ from( <method>none</method> <schema_name/> </partitioning> - <connection>dw bi indoteknik</connection> - <sql>select coalesce(p.parent_category_id, 999999) as product_category_id, sum(aml.price_subtotal) as last_year -from account_move_line aml -join product p on p.product_id = aml.product_id -join account_move am on am.id = aml.move_id -where 1=1 -and aml.parent_state = 'posted' -and am.move_type = 'out_invoice' -and aml.date between to_timestamp(get_first_day_last_year(), 'YYYY-MM-DD') and to_timestamp(get_now_last_year(), 'YYYY-MM-DD') -group by p.parent_category_id</sql> - <limit>0</limit> - <lookup/> - <execute_each_row>N</execute_each_row> - <variables_active>N</variables_active> - <lazy_conversion_active>N</lazy_conversion_active> - <cached_row_meta_active>N</cached_row_meta_active> - <row-meta> - <value-meta> - <type>Integer</type> - <storagetype>normal</storagetype> - <name>product_category_id</name> - <length>9</length> - <precision>0</precision> - <origin>last year</origin> - <comments>product_category_id</comments> - <conversion_Mask>####0;-####0</conversion_Mask> - <decimal_symbol>.</decimal_symbol> - <grouping_symbol>,</grouping_symbol> - <currency_symbol/> - <trim_type>none</trim_type> - <case_insensitive>N</case_insensitive> - <collator_disabled>Y</collator_disabled> - <collator_strength>0</collator_strength> - <sort_descending>N</sort_descending> - <output_padding>N</output_padding> - <date_format_lenient>N</date_format_lenient> - <date_format_locale>en_US</date_format_locale> - <date_format_timezone>Asia/Bangkok</date_format_timezone> - <lenient_string_to_number>N</lenient_string_to_number> - </value-meta> - <value-meta> - <type>BigNumber</type> - <storagetype>normal</storagetype> + <join_type>LEFT OUTER</join_type> + <step1>Merge join</step1> + <step2>Sort rows 3</step2> + <keys_1> + <key>id</key> + </keys_1> + <keys_2> + <key>product_category_id</key> + </keys_2> + <attributes/> + <cluster_schema/> + <remotesteps> + <input> + </input> + <output> + </output> + </remotesteps> + <GUI> + <xloc>496</xloc> + <yloc>288</yloc> + <draw>Y</draw> + </GUI> + </step> + <step> + <name>Merge join 3</name> + <type>MergeJoin</type> + <description/> + <distribute>Y</distribute> + <custom_distribution/> + <copies>1</copies> + <partitioning> + <method>none</method> + <schema_name/> + </partitioning> + <join_type>LEFT OUTER</join_type> + <step1>Merge join 2</step1> + <step2>Sort rows 4</step2> + <keys_1> + <key>id</key> + </keys_1> + <keys_2> + <key>id</key> + </keys_2> + <attributes/> + <cluster_schema/> + <remotesteps> + <input> + </input> + <output> + </output> + </remotesteps> + <GUI> + <xloc>624</xloc> + <yloc>288</yloc> + <draw>Y</draw> + </GUI> + </step> + <step> + <name>Merge join 4</name> + <type>MergeJoin</type> + <description/> + <distribute>Y</distribute> + <custom_distribution/> + <copies>1</copies> + <partitioning> + <method>none</method> + <schema_name/> + </partitioning> + <join_type>LEFT OUTER</join_type> + <step1>Merge join 3</step1> + <step2>Sort rows 5</step2> + <keys_1> + <key>id</key> + </keys_1> + <keys_2> + <key>product_category_id</key> + </keys_2> + <attributes/> + <cluster_schema/> + <remotesteps> + <input> + </input> + <output> + </output> + </remotesteps> + <GUI> + <xloc>752</xloc> + <yloc>288</yloc> + <draw>Y</draw> + </GUI> + </step> + <step> + <name>Merge join 5</name> + <type>MergeJoin</type> + <description/> + <distribute>N</distribute> + <custom_distribution/> + <copies>1</copies> + <partitioning> + <method>none</method> + <schema_name/> + </partitioning> + <join_type>LEFT OUTER</join_type> + <step1>Merge join 4</step1> + <step2>Sort rows 6</step2> + <keys_1> + <key>id</key> + </keys_1> + <keys_2> + <key>product_category_id</key> + </keys_2> + <attributes/> + <cluster_schema/> + <remotesteps> + <input> + </input> + <output> + </output> + </remotesteps> + <GUI> + <xloc>864</xloc> + <yloc>288</yloc> + <draw>Y</draw> + </GUI> + </step> + <step> + <name>Merge join 6</name> + <type>MergeJoin</type> + <description/> + <distribute>Y</distribute> + <custom_distribution/> + <copies>1</copies> + <partitioning> + <method>none</method> + <schema_name/> + </partitioning> + <join_type>LEFT OUTER</join_type> + <step1>Merge join 5</step1> + <step2>Sort rows 7</step2> + <keys_1> + <key>id</key> + </keys_1> + <keys_2> + <key>product_category_id</key> + </keys_2> + <attributes/> + <cluster_schema/> + <remotesteps> + <input> + </input> + <output> + </output> + </remotesteps> + <GUI> + <xloc>992</xloc> + <yloc>288</yloc> + <draw>Y</draw> + </GUI> + </step> + <step> + <name>Select values</name> + <type>SelectValues</type> + <description/> + <distribute>Y</distribute> + <custom_distribution/> + <copies>1</copies> + <partitioning> + <method>none</method> + <schema_name/> + </partitioning> + <fields> + <field> + <name>id</name> + </field> + <field> + <name>category</name> + </field> + <field> + <name>history_date</name> + </field> + <field> <name>last_year</name> - <length>-1</length> - <precision>-1</precision> - <origin>last year</origin> - <comments>last_year</comments> - <conversion_Mask>######0.0###################;-######0.0###################</conversion_Mask> - <decimal_symbol>.</decimal_symbol> - <grouping_symbol/> - <currency_symbol/> - <trim_type>none</trim_type> - <case_insensitive>N</case_insensitive> - <collator_disabled>Y</collator_disabled> - <collator_strength>0</collator_strength> - <sort_descending>N</sort_descending> - <output_padding>N</output_padding> - <date_format_lenient>N</date_format_lenient> - <date_format_locale>en_US</date_format_locale> - <date_format_timezone>Asia/Bangkok</date_format_timezone> - <lenient_string_to_number>N</lenient_string_to_number> - </value-meta> - </row-meta> + </field> + <field> + <name>this_year</name> + </field> + <field> + <name>target</name> + </field> + <field> + <name>trx_last_year</name> + </field> + <field> + <name>trx_this_year</name> + </field> + <field> + <name>last_year_full</name> + </field> + <select_unspecified>N</select_unspecified> + </fields> <attributes/> <cluster_schema/> <remotesteps> @@ -839,8 +1152,72 @@ group by p.parent_category_id</sql> </output> </remotesteps> <GUI> - <xloc>352</xloc> - <yloc>112</yloc> + <xloc>992</xloc> + <yloc>384</yloc> + <draw>Y</draw> + </GUI> + </step> + <step> + <name>Select values 2</name> + <type>SelectValues</type> + <description/> + <distribute>Y</distribute> + <custom_distribution/> + <copies>1</copies> + <partitioning> + <method>none</method> + <schema_name/> + </partitioning> + <fields> + <field> + <name>id</name> + </field> + <field> + <name>category</name> + </field> + <field> + <name>history_date</name> + </field> + <field> + <name>last_year</name> + </field> + <field> + <name>this_year</name> + </field> + <field> + <name>target</name> + </field> + <field> + <name>trx_last_year</name> + </field> + <field> + <name>trx_this_year</name> + </field> + <field> + <name>growth_value</name> + </field> + <field> + <name>achv_target</name> + </field> + <field> + <name>growth_trx</name> + </field> + <field> + <name>growth_value_full</name> + </field> + <select_unspecified>N</select_unspecified> + </fields> + <attributes/> + <cluster_schema/> + <remotesteps> + <input> + </input> + <output> + </output> + </remotesteps> + <GUI> + <xloc>752</xloc> + <yloc>384</yloc> <draw>Y</draw> </GUI> </step> @@ -929,8 +1306,8 @@ group by p.parent_category_id</sql> </GUI> </step> <step> - <name>Merge join</name> - <type>MergeJoin</type> + <name>Sort rows 3</name> + <type>SortRows</type> <description/> <distribute>Y</distribute> <custom_distribution/> @@ -939,15 +1316,23 @@ group by p.parent_category_id</sql> <method>none</method> <schema_name/> </partitioning> - <join_type>LEFT OUTER</join_type> - <step1>Sort rows</step1> - <step2>Sort rows 2</step2> - <keys_1> - <key>id</key> - </keys_1> - <keys_2> - <key>product_category_id</key> - </keys_2> + <directory>%%java.io.tmpdir%%</directory> + <prefix>out</prefix> + <sort_size>1000000</sort_size> + <free_memory/> + <compress>N</compress> + <compress_variable/> + <unique_rows>N</unique_rows> + <fields> + <field> + <name>product_category_id</name> + <ascending>Y</ascending> + <case_sensitive>N</case_sensitive> + <collator_enabled>N</collator_enabled> + <collator_strength>0</collator_strength> + <presorted>N</presorted> + </field> + </fields> <attributes/> <cluster_schema/> <remotesteps> @@ -957,14 +1342,14 @@ group by p.parent_category_id</sql> </output> </remotesteps> <GUI> - <xloc>352</xloc> - <yloc>288</yloc> + <xloc>496</xloc> + <yloc>208</yloc> <draw>Y</draw> </GUI> </step> <step> - <name>this year</name> - <type>TableInput</type> + <name>Sort rows 4</name> + <type>SortRows</type> <description/> <distribute>Y</distribute> <custom_distribution/> @@ -973,70 +1358,65 @@ group by p.parent_category_id</sql> <method>none</method> <schema_name/> </partitioning> - <connection>dw bi indoteknik</connection> - <sql>select coalesce(p.parent_category_id, 999999) as product_category_id, sum(aml.price_subtotal) as this_year -from account_move_line aml -join product p on p.product_id = aml.product_id -join account_move am on am.id = aml.move_id -where 1=1 -and aml.parent_state = 'posted' -and am.move_type = 'out_invoice' -and to_char(aml.date, 'YYYY') = get_now_year() -group by p.parent_category_id</sql> - <limit>0</limit> - <lookup/> - <execute_each_row>N</execute_each_row> - <variables_active>N</variables_active> - <lazy_conversion_active>N</lazy_conversion_active> - <cached_row_meta_active>N</cached_row_meta_active> - <row-meta> - <value-meta> - <type>Integer</type> - <storagetype>normal</storagetype> - <name>product_category_id</name> - <length>9</length> - <precision>0</precision> - <origin>this year</origin> - <comments>product_category_id</comments> - <conversion_Mask>####0;-####0</conversion_Mask> - <decimal_symbol>.</decimal_symbol> - <grouping_symbol>,</grouping_symbol> - <currency_symbol/> - <trim_type>none</trim_type> - <case_insensitive>N</case_insensitive> - <collator_disabled>Y</collator_disabled> + <directory>%%java.io.tmpdir%%</directory> + <prefix>out</prefix> + <sort_size>1000000</sort_size> + <free_memory/> + <compress>N</compress> + <compress_variable/> + <unique_rows>N</unique_rows> + <fields> + <field> + <name>id</name> + <ascending>Y</ascending> + <case_sensitive>N</case_sensitive> + <collator_enabled>N</collator_enabled> <collator_strength>0</collator_strength> - <sort_descending>N</sort_descending> - <output_padding>N</output_padding> - <date_format_lenient>N</date_format_lenient> - <date_format_locale>en_US</date_format_locale> - <date_format_timezone>Asia/Bangkok</date_format_timezone> - <lenient_string_to_number>N</lenient_string_to_number> - </value-meta> - <value-meta> - <type>BigNumber</type> - <storagetype>normal</storagetype> - <name>this_year</name> - <length>-1</length> - <precision>-1</precision> - <origin>this year</origin> - <comments>this_year</comments> - <conversion_Mask>######0.0###################;-######0.0###################</conversion_Mask> - <decimal_symbol>.</decimal_symbol> - <grouping_symbol/> - <currency_symbol/> - <trim_type>none</trim_type> - <case_insensitive>N</case_insensitive> - <collator_disabled>Y</collator_disabled> + <presorted>N</presorted> + </field> + </fields> + <attributes/> + <cluster_schema/> + <remotesteps> + <input> + </input> + <output> + </output> + </remotesteps> + <GUI> + <xloc>624</xloc> + <yloc>208</yloc> + <draw>Y</draw> + </GUI> + </step> + <step> + <name>Sort rows 5</name> + <type>SortRows</type> + <description/> + <distribute>Y</distribute> + <custom_distribution/> + <copies>1</copies> + <partitioning> + <method>none</method> + <schema_name/> + </partitioning> + <directory>%%java.io.tmpdir%%</directory> + <prefix>out</prefix> + <sort_size>1000000</sort_size> + <free_memory/> + <compress>N</compress> + <compress_variable/> + <unique_rows>N</unique_rows> + <fields> + <field> + <name>product_category_id</name> + <ascending>Y</ascending> + <case_sensitive>N</case_sensitive> + <collator_enabled>N</collator_enabled> <collator_strength>0</collator_strength> - <sort_descending>N</sort_descending> - <output_padding>N</output_padding> - <date_format_lenient>N</date_format_lenient> - <date_format_locale>en_US</date_format_locale> - <date_format_timezone>Asia/Bangkok</date_format_timezone> - <lenient_string_to_number>N</lenient_string_to_number> - </value-meta> - </row-meta> + <presorted>N</presorted> + </field> + </fields> <attributes/> <cluster_schema/> <remotesteps> @@ -1046,13 +1426,13 @@ group by p.parent_category_id</sql> </output> </remotesteps> <GUI> - <xloc>496</xloc> - <yloc>112</yloc> + <xloc>752</xloc> + <yloc>208</yloc> <draw>Y</draw> </GUI> </step> <step> - <name>Sort rows 3</name> + <name>Sort rows 6</name> <type>SortRows</type> <description/> <distribute>Y</distribute> @@ -1088,14 +1468,14 @@ group by p.parent_category_id</sql> </output> </remotesteps> <GUI> - <xloc>496</xloc> + <xloc>864</xloc> <yloc>208</yloc> <draw>Y</draw> </GUI> </step> <step> - <name>Merge join 2</name> - <type>MergeJoin</type> + <name>Sort rows 7</name> + <type>SortRows</type> <description/> <distribute>Y</distribute> <custom_distribution/> @@ -1104,15 +1484,23 @@ group by p.parent_category_id</sql> <method>none</method> <schema_name/> </partitioning> - <join_type>LEFT OUTER</join_type> - <step1>Merge join</step1> - <step2>Sort rows 3</step2> - <keys_1> - <key>id</key> - </keys_1> - <keys_2> - <key>product_category_id</key> - </keys_2> + <directory>%%java.io.tmpdir%%</directory> + <prefix>out</prefix> + <sort_size>1000000</sort_size> + <free_memory/> + <compress>N</compress> + <compress_variable/> + <unique_rows>N</unique_rows> + <fields> + <field> + <name>product_category_id</name> + <ascending>Y</ascending> + <case_sensitive>N</case_sensitive> + <collator_enabled>N</collator_enabled> + <collator_strength>0</collator_strength> + <presorted>N</presorted> + </field> + </fields> <attributes/> <cluster_schema/> <remotesteps> @@ -1122,8 +1510,8 @@ group by p.parent_category_id</sql> </output> </remotesteps> <GUI> - <xloc>496</xloc> - <yloc>288</yloc> + <xloc>992</xloc> + <yloc>208</yloc> <draw>Y</draw> </GUI> </step> @@ -1232,229 +1620,7 @@ group by p.parent_category_id</sql> </GUI> </step> <step> - <name>Sort rows 4</name> - <type>SortRows</type> - <description/> - <distribute>Y</distribute> - <custom_distribution/> - <copies>1</copies> - <partitioning> - <method>none</method> - <schema_name/> - </partitioning> - <directory>%%java.io.tmpdir%%</directory> - <prefix>out</prefix> - <sort_size>1000000</sort_size> - <free_memory/> - <compress>N</compress> - <compress_variable/> - <unique_rows>N</unique_rows> - <fields> - <field> - <name>id</name> - <ascending>Y</ascending> - <case_sensitive>N</case_sensitive> - <collator_enabled>N</collator_enabled> - <collator_strength>0</collator_strength> - <presorted>N</presorted> - </field> - </fields> - <attributes/> - <cluster_schema/> - <remotesteps> - <input> - </input> - <output> - </output> - </remotesteps> - <GUI> - <xloc>624</xloc> - <yloc>208</yloc> - <draw>Y</draw> - </GUI> - </step> - <step> - <name>Merge join 3</name> - <type>MergeJoin</type> - <description/> - <distribute>Y</distribute> - <custom_distribution/> - <copies>1</copies> - <partitioning> - <method>none</method> - <schema_name/> - </partitioning> - <join_type>LEFT OUTER</join_type> - <step1>Merge join 2</step1> - <step2>Sort rows 4</step2> - <keys_1> - <key>id</key> - </keys_1> - <keys_2> - <key>id</key> - </keys_2> - <attributes/> - <cluster_schema/> - <remotesteps> - <input> - </input> - <output> - </output> - </remotesteps> - <GUI> - <xloc>624</xloc> - <yloc>288</yloc> - <draw>Y</draw> - </GUI> - </step> - <step> - <name>Calculator</name> - <type>Calculator</type> - <description/> - <distribute>Y</distribute> - <custom_distribution/> - <copies>1</copies> - <partitioning> - <method>none</method> - <schema_name/> - </partitioning> - <failIfNoFile>Y</failIfNoFile> - <calculation> - <field_name>diff_ly</field_name> - <calc_type>SUBTRACT</calc_type> - <field_a>this_year</field_a> - <field_b>last_year</field_b> - <field_c/> - <value_type>Number</value_type> - <value_length>-1</value_length> - <value_precision>-1</value_precision> - <remove>N</remove> - <conversion_mask/> - <decimal_symbol/> - <grouping_symbol/> - <currency_symbol/> - </calculation> - <calculation> - <field_name>growth_value</field_name> - <calc_type>DIVIDE</calc_type> - <field_a>diff_ly</field_a> - <field_b>last_year</field_b> - <field_c/> - <value_type>Number</value_type> - <value_length>-1</value_length> - <value_precision>-1</value_precision> - <remove>N</remove> - <conversion_mask/> - <decimal_symbol/> - <grouping_symbol/> - <currency_symbol/> - </calculation> - <calculation> - <field_name>diff_target</field_name> - <calc_type>SUBTRACT</calc_type> - <field_a>this_year</field_a> - <field_b>target</field_b> - <field_c/> - <value_type>Number</value_type> - <value_length>-1</value_length> - <value_precision>-1</value_precision> - <remove>N</remove> - <conversion_mask/> - <decimal_symbol/> - <grouping_symbol/> - <currency_symbol/> - </calculation> - <calculation> - <field_name>achv_target</field_name> - <calc_type>DIVIDE</calc_type> - <field_a>diff_target</field_a> - <field_b>target</field_b> - <field_c/> - <value_type>Number</value_type> - <value_length>-1</value_length> - <value_precision>-1</value_precision> - <remove>N</remove> - <conversion_mask/> - <decimal_symbol/> - <grouping_symbol/> - <currency_symbol/> - </calculation> - <calculation> - <field_name>diff_trx</field_name> - <calc_type>SUBTRACT</calc_type> - <field_a>trx_this_year</field_a> - <field_b>trx_last_year</field_b> - <field_c/> - <value_type>Number</value_type> - <value_length>-1</value_length> - <value_precision>-1</value_precision> - <remove>N</remove> - <conversion_mask/> - <decimal_symbol/> - <grouping_symbol/> - <currency_symbol/> - </calculation> - <calculation> - <field_name>growth_trx</field_name> - <calc_type>DIVIDE</calc_type> - <field_a>diff_trx</field_a> - <field_b>trx_last_year</field_b> - <field_c/> - <value_type>Number</value_type> - <value_length>-1</value_length> - <value_precision>-1</value_precision> - <remove>N</remove> - <conversion_mask/> - <decimal_symbol/> - <grouping_symbol/> - <currency_symbol/> - </calculation> - <calculation> - <field_name>diff_ly_full</field_name> - <calc_type>SUBTRACT</calc_type> - <field_a>this_year</field_a> - <field_b>last_year_full</field_b> - <field_c/> - <value_type>Number</value_type> - <value_length>-1</value_length> - <value_precision>-1</value_precision> - <remove>N</remove> - <conversion_mask/> - <decimal_symbol/> - <grouping_symbol/> - <currency_symbol/> - </calculation> - <calculation> - <field_name>growth_value_full</field_name> - <calc_type>DIVIDE</calc_type> - <field_a>diff_ly_full</field_a> - <field_b>last_year_full</field_b> - <field_c/> - <value_type>Number</value_type> - <value_length>-1</value_length> - <value_precision>-1</value_precision> - <remove>N</remove> - <conversion_mask/> - <decimal_symbol/> - <grouping_symbol/> - <currency_symbol/> - </calculation> - <attributes/> - <cluster_schema/> - <remotesteps> - <input> - </input> - <output> - </output> - </remotesteps> - <GUI> - <xloc>864</xloc> - <yloc>384</yloc> - <draw>Y</draw> - </GUI> - </step> - <step> - <name>trx last year</name> + <name>last year</name> <type>TableInput</type> <description/> <distribute>Y</distribute> @@ -1465,7 +1631,7 @@ group by p.parent_category_id</sql> <schema_name/> </partitioning> <connection>dw bi indoteknik</connection> - <sql>select coalesce(p.parent_category_id, 999999) as product_category_id, count(distinct aml.move_id) as trx_last_year + <sql>select coalesce(p.parent_category_id, 999999) as product_category_id, sum(aml.price_subtotal) as last_year from account_move_line aml join product p on p.product_id = aml.product_id join account_move am on am.id = aml.move_id @@ -1487,7 +1653,7 @@ group by p.parent_category_id</sql> <name>product_category_id</name> <length>9</length> <precision>0</precision> - <origin>trx last year</origin> + <origin>last year</origin> <comments>product_category_id</comments> <conversion_Mask>####0;-####0</conversion_Mask> <decimal_symbol>.</decimal_symbol> @@ -1505,16 +1671,16 @@ group by p.parent_category_id</sql> <lenient_string_to_number>N</lenient_string_to_number> </value-meta> <value-meta> - <type>Integer</type> + <type>BigNumber</type> <storagetype>normal</storagetype> - <name>trx_last_year</name> - <length>15</length> - <precision>0</precision> - <origin>trx last year</origin> - <comments>trx_last_year</comments> - <conversion_Mask>####0;-####0</conversion_Mask> + <name>last_year</name> + <length>-1</length> + <precision>-1</precision> + <origin>last year</origin> + <comments>last_year</comments> + <conversion_Mask>######0.0###################;-######0.0###################</conversion_Mask> <decimal_symbol>.</decimal_symbol> - <grouping_symbol>,</grouping_symbol> + <grouping_symbol/> <currency_symbol/> <trim_type>none</trim_type> <case_insensitive>N</case_insensitive> @@ -1537,13 +1703,13 @@ group by p.parent_category_id</sql> </output> </remotesteps> <GUI> - <xloc>752</xloc> + <xloc>352</xloc> <yloc>112</yloc> <draw>Y</draw> </GUI> </step> <step> - <name>trx this year</name> + <name>last year full</name> <type>TableInput</type> <description/> <distribute>Y</distribute> @@ -1554,14 +1720,14 @@ group by p.parent_category_id</sql> <schema_name/> </partitioning> <connection>dw bi indoteknik</connection> - <sql>select coalesce(p.parent_category_id, 999999) as product_category_id, count(distinct aml.move_id) as trx_this_year + <sql>select coalesce(p.parent_category_id, 999999) as product_category_id, sum(aml.price_subtotal) as last_year_full from account_move_line aml join product p on p.product_id = aml.product_id join account_move am on am.id = aml.move_id where 1=1 and aml.parent_state = 'posted' and am.move_type = 'out_invoice' -and to_char(aml.date, 'YYYY') = get_now_year() +and to_char(aml.date, 'YYYY') = get_last_year() group by p.parent_category_id</sql> <limit>0</limit> <lookup/> @@ -1576,7 +1742,7 @@ group by p.parent_category_id</sql> <name>product_category_id</name> <length>9</length> <precision>0</precision> - <origin>trx this year</origin> + <origin>last year full</origin> <comments>product_category_id</comments> <conversion_Mask>####0;-####0</conversion_Mask> <decimal_symbol>.</decimal_symbol> @@ -1594,16 +1760,16 @@ group by p.parent_category_id</sql> <lenient_string_to_number>N</lenient_string_to_number> </value-meta> <value-meta> - <type>Integer</type> + <type>BigNumber</type> <storagetype>normal</storagetype> - <name>trx_this_year</name> - <length>15</length> - <precision>0</precision> - <origin>trx this year</origin> - <comments>trx_this_year</comments> - <conversion_Mask>####0;-####0</conversion_Mask> + <name>last_year_full</name> + <length>-1</length> + <precision>-1</precision> + <origin>last year full</origin> + <comments>last_year_full</comments> + <conversion_Mask>######0.0###################;-######0.0###################</conversion_Mask> <decimal_symbol>.</decimal_symbol> - <grouping_symbol>,</grouping_symbol> + <grouping_symbol/> <currency_symbol/> <trim_type>none</trim_type> <case_insensitive>N</case_insensitive> @@ -1626,14 +1792,14 @@ group by p.parent_category_id</sql> </output> </remotesteps> <GUI> - <xloc>864</xloc> + <xloc>992</xloc> <yloc>112</yloc> <draw>Y</draw> </GUI> </step> <step> - <name>Sort rows 5</name> - <type>SortRows</type> + <name>parent category</name> + <type>TableInput</type> <description/> <distribute>Y</distribute> <custom_distribution/> @@ -1642,133 +1808,82 @@ group by p.parent_category_id</sql> <method>none</method> <schema_name/> </partitioning> - <directory>%%java.io.tmpdir%%</directory> - <prefix>out</prefix> - <sort_size>1000000</sort_size> - <free_memory/> - <compress>N</compress> - <compress_variable/> - <unique_rows>N</unique_rows> - <fields> - <field> - <name>product_category_id</name> - <ascending>Y</ascending> - <case_sensitive>N</case_sensitive> - <collator_enabled>N</collator_enabled> + <connection>erp indoteknik production</connection> + <sql>select id, category, now()::timestamp-'7 hours'::interval as history_date +from( + select ppc.id, name as category + from product_public_category ppc + where ppc.id in( + 2040, + 2097, + 2161, + 2222, + 2246, + 2273, + 2315, + 2354, + 2394, + 2420, + 2477, + 2445 + ) + union + select 999999, 'Others' +)a order by id</sql> + <limit>0</limit> + <lookup/> + <execute_each_row>N</execute_each_row> + <variables_active>N</variables_active> + <lazy_conversion_active>N</lazy_conversion_active> + <cached_row_meta_active>N</cached_row_meta_active> + <row-meta> + <value-meta> + <type>Integer</type> + <storagetype>normal</storagetype> + <name>id</name> + <length>9</length> + <precision>0</precision> + <origin>parent category</origin> + <comments>id</comments> + <conversion_Mask>####0;-####0</conversion_Mask> + <decimal_symbol>.</decimal_symbol> + <grouping_symbol>,</grouping_symbol> + <currency_symbol/> + <trim_type>none</trim_type> + <case_insensitive>N</case_insensitive> + <collator_disabled>Y</collator_disabled> <collator_strength>0</collator_strength> - <presorted>N</presorted> - </field> - </fields> - <attributes/> - <cluster_schema/> - <remotesteps> - <input> - </input> - <output> - </output> - </remotesteps> - <GUI> - <xloc>752</xloc> - <yloc>208</yloc> - <draw>Y</draw> - </GUI> - </step> - <step> - <name>Sort rows 6</name> - <type>SortRows</type> - <description/> - <distribute>Y</distribute> - <custom_distribution/> - <copies>1</copies> - <partitioning> - <method>none</method> - <schema_name/> - </partitioning> - <directory>%%java.io.tmpdir%%</directory> - <prefix>out</prefix> - <sort_size>1000000</sort_size> - <free_memory/> - <compress>N</compress> - <compress_variable/> - <unique_rows>N</unique_rows> - <fields> - <field> - <name>product_category_id</name> - <ascending>Y</ascending> - <case_sensitive>N</case_sensitive> - <collator_enabled>N</collator_enabled> + <sort_descending>N</sort_descending> + <output_padding>N</output_padding> + <date_format_lenient>N</date_format_lenient> + <date_format_locale>en_US</date_format_locale> + <date_format_timezone>Asia/Bangkok</date_format_timezone> + <lenient_string_to_number>N</lenient_string_to_number> + </value-meta> + <value-meta> + <type>String</type> + <storagetype>normal</storagetype> + <name>category</name> + <length>2147483647</length> + <precision>-1</precision> + <origin>parent category</origin> + <comments>category</comments> + <conversion_Mask/> + <decimal_symbol>.</decimal_symbol> + <grouping_symbol>,</grouping_symbol> + <currency_symbol/> + <trim_type>none</trim_type> + <case_insensitive>N</case_insensitive> + <collator_disabled>Y</collator_disabled> <collator_strength>0</collator_strength> - <presorted>N</presorted> - </field> - </fields> - <attributes/> - <cluster_schema/> - <remotesteps> - <input> - </input> - <output> - </output> - </remotesteps> - <GUI> - <xloc>864</xloc> - <yloc>208</yloc> - <draw>Y</draw> - </GUI> - </step> - <step> - <name>Merge join 4</name> - <type>MergeJoin</type> - <description/> - <distribute>Y</distribute> - <custom_distribution/> - <copies>1</copies> - <partitioning> - <method>none</method> - <schema_name/> - </partitioning> - <join_type>LEFT OUTER</join_type> - <step1>Merge join 3</step1> - <step2>Sort rows 5</step2> - <keys_1> - <key>id</key> - </keys_1> - <keys_2> - <key>product_category_id</key> - </keys_2> - <attributes/> - <cluster_schema/> - <remotesteps> - <input> - </input> - <output> - </output> - </remotesteps> - <GUI> - <xloc>752</xloc> - <yloc>288</yloc> - <draw>Y</draw> - </GUI> - </step> - <step> - <name>Merge join 5</name> - <type>MergeJoin</type> - <description/> - <distribute>N</distribute> - <custom_distribution/> - <copies>1</copies> - <partitioning> - <method>none</method> - <schema_name/> - </partitioning> - <join_type>LEFT OUTER</join_type> - <step1>Merge join 4</step1> - <step2>Sort rows 6</step2> - <keys_1> - <key>id</key> - </keys_1> - <keys_2> - <key>product_category_id</key> - </keys_2> + <sort_descending>N</sort_descending> + <output_padding>N</output_padding> + <date_format_lenient>N</date_format_lenient> + <date_format_locale>en_US</date_format_locale> + <date_format_timezone>Asia/Bangkok</date_format_timezone> + <lenient_string_to_number>N</lenient_string_to_number> + </value-meta> + </row-meta> <attributes/> <cluster_schema/> <remotesteps> @@ -1778,251 +1893,13 @@ group by p.parent_category_id</sql> </output> </remotesteps> <GUI> - <xloc>864</xloc> + <xloc>160</xloc> <yloc>288</yloc> <draw>Y</draw> </GUI> </step> <step> - <name>Select values</name> - <type>SelectValues</type> - <description/> - <distribute>Y</distribute> - <custom_distribution/> - <copies>1</copies> - <partitioning> - <method>none</method> - <schema_name/> - </partitioning> - <fields> - <field> - <name>id</name> - <rename/> - </field> - <field> - <name>category</name> - <rename/> - </field> - <field> - <name>history_date</name> - <rename/> - </field> - <field> - <name>last_year</name> - <rename/> - </field> - <field> - <name>this_year</name> - <rename/> - </field> - <field> - <name>target</name> - <rename/> - </field> - <field> - <name>trx_last_year</name> - <rename/> - </field> - <field> - <name>trx_this_year</name> - <rename/> - </field> - <field> - <name>last_year_full</name> - <rename/> - </field> - <select_unspecified>N</select_unspecified> - </fields> - <attributes/> - <cluster_schema/> - <remotesteps> - <input> - </input> - <output> - </output> - </remotesteps> - <GUI> - <xloc>992</xloc> - <yloc>384</yloc> - <draw>Y</draw> - </GUI> - </step> - <step> - <name>Select values 2</name> - <type>SelectValues</type> - <description/> - <distribute>Y</distribute> - <custom_distribution/> - <copies>1</copies> - <partitioning> - <method>none</method> - <schema_name/> - </partitioning> - <fields> - <field> - <name>id</name> - <rename/> - </field> - <field> - <name>category</name> - <rename/> - </field> - <field> - <name>history_date</name> - <rename/> - </field> - <field> - <name>last_year</name> - <rename/> - </field> - <field> - <name>this_year</name> - <rename/> - </field> - <field> - <name>target</name> - <rename/> - </field> - <field> - <name>trx_last_year</name> - <rename/> - </field> - <field> - <name>trx_this_year</name> - <rename/> - </field> - <field> - <name>growth_value</name> - <rename/> - </field> - <field> - <name>achv_target</name> - <rename/> - </field> - <field> - <name>growth_trx</name> - <rename/> - </field> - <field> - <name>growth_value_full</name> - <rename/> - </field> - <select_unspecified>N</select_unspecified> - </fields> - <attributes/> - <cluster_schema/> - <remotesteps> - <input> - </input> - <output> - </output> - </remotesteps> - <GUI> - <xloc>752</xloc> - <yloc>384</yloc> - <draw>Y</draw> - </GUI> - </step> - <step> - <name>Insert / update</name> - <type>InsertUpdate</type> - <description/> - <distribute>Y</distribute> - <custom_distribution/> - <copies>1</copies> - <partitioning> - <method>none</method> - <schema_name/> - </partitioning> - <connection>dw bi indoteknik</connection> - <commit>100</commit> - <update_bypassed>N</update_bypassed> - <lookup> - <schema>public</schema> - <table>t_omset_by_parent_category</table> - <key> - <name>id</name> - <field>id</field> - <condition>=</condition> - <name2/> - </key> - <value> - <name>id</name> - <rename>id</rename> - <update>N</update> - </value> - <value> - <name>category</name> - <rename>category</rename> - <update>Y</update> - </value> - <value> - <name>history_date</name> - <rename>history_date</rename> - <update>Y</update> - </value> - <value> - <name>last_year</name> - <rename>last_year</rename> - <update>Y</update> - </value> - <value> - <name>this_year</name> - <rename>this_year</rename> - <update>Y</update> - </value> - <value> - <name>target</name> - <rename>target</rename> - <update>Y</update> - </value> - <value> - <name>trx_last_year</name> - <rename>trx_last_year</rename> - <update>Y</update> - </value> - <value> - <name>trx_this_year</name> - <rename>trx_this_year</rename> - <update>Y</update> - </value> - <value> - <name>growth_value</name> - <rename>growth_value</rename> - <update>Y</update> - </value> - <value> - <name>achv_target</name> - <rename>achv_target</rename> - <update>Y</update> - </value> - <value> - <name>growth_trx</name> - <rename>growth_trx</rename> - <update>Y</update> - </value> - <value> - <name>growth_value_full</name> - <rename>growth_value_full</rename> - <update>Y</update> - </value> - </lookup> - <attributes/> - <cluster_schema/> - <remotesteps> - <input> - </input> - <output> - </output> - </remotesteps> - <GUI> - <xloc>624</xloc> - <yloc>384</yloc> - <draw>Y</draw> - </GUI> - </step> - <step> - <name>last year full</name> + <name>this year</name> <type>TableInput</type> <description/> <distribute>Y</distribute> @@ -2033,14 +1910,14 @@ group by p.parent_category_id</sql> <schema_name/> </partitioning> <connection>dw bi indoteknik</connection> - <sql>select coalesce(p.parent_category_id, 999999) as product_category_id, sum(aml.price_subtotal) as last_year_full + <sql>select coalesce(p.parent_category_id, 999999) as product_category_id, sum(aml.price_subtotal) as this_year from account_move_line aml join product p on p.product_id = aml.product_id join account_move am on am.id = aml.move_id where 1=1 and aml.parent_state = 'posted' and am.move_type = 'out_invoice' -and to_char(aml.date, 'YYYY') = get_last_year() +and to_char(aml.date, 'YYYY') = get_now_year() group by p.parent_category_id</sql> <limit>0</limit> <lookup/> @@ -2055,7 +1932,7 @@ group by p.parent_category_id</sql> <name>product_category_id</name> <length>9</length> <precision>0</precision> - <origin>last year full</origin> + <origin>this year</origin> <comments>product_category_id</comments> <conversion_Mask>####0;-####0</conversion_Mask> <decimal_symbol>.</decimal_symbol> @@ -2075,11 +1952,11 @@ group by p.parent_category_id</sql> <value-meta> <type>BigNumber</type> <storagetype>normal</storagetype> - <name>last_year_full</name> + <name>this_year</name> <length>-1</length> <precision>-1</precision> - <origin>last year full</origin> - <comments>last_year_full</comments> + <origin>this year</origin> + <comments>this_year</comments> <conversion_Mask>######0.0###################;-######0.0###################</conversion_Mask> <decimal_symbol>.</decimal_symbol> <grouping_symbol/> @@ -2105,14 +1982,14 @@ group by p.parent_category_id</sql> </output> </remotesteps> <GUI> - <xloc>992</xloc> + <xloc>496</xloc> <yloc>112</yloc> <draw>Y</draw> </GUI> </step> <step> - <name>Sort rows 7</name> - <type>SortRows</type> + <name>trx last year</name> + <type>TableInput</type> <description/> <distribute>Y</distribute> <custom_distribution/> @@ -2121,23 +1998,70 @@ group by p.parent_category_id</sql> <method>none</method> <schema_name/> </partitioning> - <directory>%%java.io.tmpdir%%</directory> - <prefix>out</prefix> - <sort_size>1000000</sort_size> - <free_memory/> - <compress>N</compress> - <compress_variable/> - <unique_rows>N</unique_rows> - <fields> - <field> + <connection>dw bi indoteknik</connection> + <sql>select coalesce(p.parent_category_id, 999999) as product_category_id, count(distinct aml.move_id) as trx_last_year +from account_move_line aml +join product p on p.product_id = aml.product_id +join account_move am on am.id = aml.move_id +where 1=1 +and aml.parent_state = 'posted' +and am.move_type = 'out_invoice' +and aml.date between to_timestamp(get_first_day_last_year(), 'YYYY-MM-DD') and to_timestamp(get_now_last_year(), 'YYYY-MM-DD') +group by p.parent_category_id</sql> + <limit>0</limit> + <lookup/> + <execute_each_row>N</execute_each_row> + <variables_active>N</variables_active> + <lazy_conversion_active>N</lazy_conversion_active> + <cached_row_meta_active>N</cached_row_meta_active> + <row-meta> + <value-meta> + <type>Integer</type> + <storagetype>normal</storagetype> <name>product_category_id</name> - <ascending>Y</ascending> - <case_sensitive>N</case_sensitive> - <collator_enabled>N</collator_enabled> + <length>9</length> + <precision>0</precision> + <origin>trx last year</origin> + <comments>product_category_id</comments> + <conversion_Mask>####0;-####0</conversion_Mask> + <decimal_symbol>.</decimal_symbol> + <grouping_symbol>,</grouping_symbol> + <currency_symbol/> + <trim_type>none</trim_type> + <case_insensitive>N</case_insensitive> + <collator_disabled>Y</collator_disabled> <collator_strength>0</collator_strength> - <presorted>N</presorted> - </field> - </fields> + <sort_descending>N</sort_descending> + <output_padding>N</output_padding> + <date_format_lenient>N</date_format_lenient> + <date_format_locale>en_US</date_format_locale> + <date_format_timezone>Asia/Bangkok</date_format_timezone> + <lenient_string_to_number>N</lenient_string_to_number> + </value-meta> + <value-meta> + <type>Integer</type> + <storagetype>normal</storagetype> + <name>trx_last_year</name> + <length>15</length> + <precision>0</precision> + <origin>trx last year</origin> + <comments>trx_last_year</comments> + <conversion_Mask>####0;-####0</conversion_Mask> + <decimal_symbol>.</decimal_symbol> + <grouping_symbol>,</grouping_symbol> + <currency_symbol/> + <trim_type>none</trim_type> + <case_insensitive>N</case_insensitive> + <collator_disabled>Y</collator_disabled> + <collator_strength>0</collator_strength> + <sort_descending>N</sort_descending> + <output_padding>N</output_padding> + <date_format_lenient>N</date_format_lenient> + <date_format_locale>en_US</date_format_locale> + <date_format_timezone>Asia/Bangkok</date_format_timezone> + <lenient_string_to_number>N</lenient_string_to_number> + </value-meta> + </row-meta> <attributes/> <cluster_schema/> <remotesteps> @@ -2147,14 +2071,14 @@ group by p.parent_category_id</sql> </output> </remotesteps> <GUI> - <xloc>992</xloc> - <yloc>208</yloc> + <xloc>752</xloc> + <yloc>112</yloc> <draw>Y</draw> </GUI> </step> <step> - <name>Merge join 6</name> - <type>MergeJoin</type> + <name>trx this year</name> + <type>TableInput</type> <description/> <distribute>Y</distribute> <custom_distribution/> @@ -2163,15 +2087,70 @@ group by p.parent_category_id</sql> <method>none</method> <schema_name/> </partitioning> - <join_type>LEFT OUTER</join_type> - <step1>Merge join 5</step1> - <step2>Sort rows 7</step2> - <keys_1> - <key>id</key> - </keys_1> - <keys_2> - <key>product_category_id</key> - </keys_2> + <connection>dw bi indoteknik</connection> + <sql>select coalesce(p.parent_category_id, 999999) as product_category_id, count(distinct aml.move_id) as trx_this_year +from account_move_line aml +join product p on p.product_id = aml.product_id +join account_move am on am.id = aml.move_id +where 1=1 +and aml.parent_state = 'posted' +and am.move_type = 'out_invoice' +and to_char(aml.date, 'YYYY') = get_now_year() +group by p.parent_category_id</sql> + <limit>0</limit> + <lookup/> + <execute_each_row>N</execute_each_row> + <variables_active>N</variables_active> + <lazy_conversion_active>N</lazy_conversion_active> + <cached_row_meta_active>N</cached_row_meta_active> + <row-meta> + <value-meta> + <type>Integer</type> + <storagetype>normal</storagetype> + <name>product_category_id</name> + <length>9</length> + <precision>0</precision> + <origin>trx this year</origin> + <comments>product_category_id</comments> + <conversion_Mask>####0;-####0</conversion_Mask> + <decimal_symbol>.</decimal_symbol> + <grouping_symbol>,</grouping_symbol> + <currency_symbol/> + <trim_type>none</trim_type> + <case_insensitive>N</case_insensitive> + <collator_disabled>Y</collator_disabled> + <collator_strength>0</collator_strength> + <sort_descending>N</sort_descending> + <output_padding>N</output_padding> + <date_format_lenient>N</date_format_lenient> + <date_format_locale>en_US</date_format_locale> + <date_format_timezone>Asia/Bangkok</date_format_timezone> + <lenient_string_to_number>N</lenient_string_to_number> + </value-meta> + <value-meta> + <type>Integer</type> + <storagetype>normal</storagetype> + <name>trx_this_year</name> + <length>15</length> + <precision>0</precision> + <origin>trx this year</origin> + <comments>trx_this_year</comments> + <conversion_Mask>####0;-####0</conversion_Mask> + <decimal_symbol>.</decimal_symbol> + <grouping_symbol>,</grouping_symbol> + <currency_symbol/> + <trim_type>none</trim_type> + <case_insensitive>N</case_insensitive> + <collator_disabled>Y</collator_disabled> + <collator_strength>0</collator_strength> + <sort_descending>N</sort_descending> + <output_padding>N</output_padding> + <date_format_lenient>N</date_format_lenient> + <date_format_locale>en_US</date_format_locale> + <date_format_timezone>Asia/Bangkok</date_format_timezone> + <lenient_string_to_number>N</lenient_string_to_number> + </value-meta> + </row-meta> <attributes/> <cluster_schema/> <remotesteps> @@ -2181,8 +2160,8 @@ group by p.parent_category_id</sql> </output> </remotesteps> <GUI> - <xloc>992</xloc> - <yloc>288</yloc> + <xloc>864</xloc> + <yloc>112</yloc> <draw>Y</draw> </GUI> </step> |