6 Replies Latest reply: Oct 14, 2017 4:58 PM by Philippe BONNIN RSS

    SELECTING FORECAST IN LOADING SCRIPT

    Philippe BONNIN

      Hi

       

      i have 3 TAB :

       

       

      first and second are CURRENT AND FORECAST sales and customers

      CURRENT.pngFORECAST.png

       

       

      As you see the user have choose several forecast  (1 and 2)

       

      Moreover, the fiscal year begins in March

       

       

       

      The third TAB gives the fiscal years and the number of the forecast simulations

       

      SELECT.png

       

      My loading script is this one for the moment

      [C_S]:

      LOAD Date(MakeDate([CY_YEAR], [CY_MONTH]), 'MM_YYYY') as MonthYear,

      [CY_YEAR],

          [CY_MONTH],

      [C_SALES],

      [C_CUSTOMERS]

      FROM [lib://LOAD_SIM/CURRENT_DATAS.xlsx]

      (ooxml, embedded labels, table is C_S);

       

       

      [F_S]:

      LOAD Date(MakeDate([f_YEAR], [f_MONTH]), 'MM_YYYY') as MonthYear,

      [f_YEAR],

      [f_MONTH],

          [N_SIM],

      [f_SALES],

      [f_CUSTOMERS]

      FROM [lib://LOAD_SIM/FORECAST_DATAS.xlsx]

      (ooxml, embedded labels, table is F_S);

       

       

      [FISC]:

      LOAD Date(MakeDate([F_YEAR]), 'YYYY') as Year,

      [F_YEAR],

      [MONTH_START],

          [N_SIMUL],

      [SIM_LIB],

      [MONTH_FINISH],

      [SELECT]

      FROM [lib://LOAD_SIM/SELECT_DATAS.xlsx]

      (ooxml, embedded labels, table is FISC);

       

       

      My issue :

       

      I want to load from the FORECAST TAB , only the rows of the forecast selected in red in tab 3 (  2016 and 2017 i want only the simul 2, )


      in other words  :


      I want to load only one FORECAST per year ... the selected one



      THANK'S IN ADVANCE


      Philippe


        • Re: SELECTING FORECAST IN LOADING SCRIPT
          Anand Chouhan

          What is final output you need elaborate or try this also for FISC table

           

          [FISC]:

          LOAD Date(MakeDate([F_YEAR]), 'YYYY') as Year,

          [F_YEAR],

          [MONTH_START],

              [N_SIMUL],

          [SIM_LIB],

          [MONTH_FINISH],

          [SELECT]

          FROM [lib://LOAD_SIM/SELECT_DATAS.xlsx]

          (ooxml, embedded labels, table is FISC)

          Where [SELECT] = 1;

            • Re: SELECTING FORECAST IN LOADING SCRIPT
              Philippe BONNIN

              Your script select rows in the third tab 

               

              What i want is to select rows in the second TAB (forecast) taking into account the selection in the third TAB

               

              if you prefer, i want to load ONLY the higtlighted rows below     (table extract)

              SELECTED_ROWS.png

              Regards

              Philippe

                • Re: SELECTING FORECAST IN LOADING SCRIPT
                  Anand Chouhan

                  Use Where Exists

                   

                  [FISC_Code]:

                  LOAD

                  [N_SIMUL]

                  FROM [lib://LOAD_SIM/SELECT_DATAS.xlsx]

                  (ooxml, embedded labels, table is FISC)

                  Where [SELECT] = 1;

                   

                  [F_S]:

                  LOAD Date(MakeDate([f_YEAR], [f_MONTH]), 'MM_YYYY') as MonthYear,

                  [f_YEAR],

                  [f_MONTH],

                  [N_SIM],

                  [f_SALES],

                  [f_CUSTOMERS]

                  FROM [lib://LOAD_SIM/FORECAST_DATAS.xlsx]

                  (ooxml, embedded labels, table is F_S)

                  Where Exists( [N_SIMUL],[N_SIM] );


                    • Re: SELECTING FORECAST IN LOADING SCRIPT
                      Philippe BONNIN

                      Are you sure ?

                       

                      the first "where[select] = 1 loads

                       

                      SELECTWHERE.png

                      i always have 1 and 2 in field N_SIMUL

                       

                      So i think that  Where Exists( [N_SIMUL],[N_SIM] ) does not any selection in the forecast tab ...

                       

                      here is the result :


                      SELECT3.png

                       

                      Any other idea ?

                        • Re: SELECTING FORECAST IN LOADING SCRIPT
                          Anand Chouhan

                          Oh this bad may be

                           

                          [FISC_Code]:

                          LOAD

                          [N_SIMUL]

                          FROM [lib://LOAD_SIM/SELECT_DATAS.xlsx]

                          (ooxml, embedded labels, table is FISC);

                           

                          [F_S]:

                          LOAD Date(MakeDate([f_YEAR], [f_MONTH]), 'MM_YYYY') as MonthYear,

                          [f_YEAR],

                          [f_MONTH],

                          [N_SIM],

                          [f_SALES],

                          [f_CUSTOMERS]

                          FROM [lib://LOAD_SIM/FORECAST_DATAS.xlsx]

                          (ooxml, embedded labels, table is F_S)

                          Where Exists( [N_SIMUL],[N_SIM] );

                            • Re: SELECTING FORECAST IN LOADING SCRIPT
                              Philippe BONNIN

                              same result :-(

                               

                              i don't understand how it could be work ?... the "whereexist"  is apply on equivalent numbers in the 2 fields ( 1 and 2 ) ?...

                              May be it would be useful to link the 2 tab (N_SIMUL As N_SIM) before use where or where exists ?

                               

                              another difficulty is that in the third tab we have DATE as Fiscal Year (begginning in March) and in the forecast tab we have MonthYear ...

                               

                              I do not understand how to use the selection of the third table to eliminate rows month by month in the forecast table