27 Replies Latest reply: Oct 18, 2017 9:51 AM by Philippe BONNIN RSS

    LOADING SELECTED VALUES

    Philippe BONNIN

      hi everybody

       

      i have 2 tabs

       

      the first with datas (e.g. SALES  ) and dates as Month & Year

       

      You'll see there are 2 specificities :

       

      - mobile years March to February

      - 2 categories of data existing or not depending on the mobile years

      DATAS_CAT.png



      The second tabs give the selected categorie (only one per mobile year to use in my app)

      DATA_SELECT.png



      My issue


      i want to have a script that load only the selected categorie in the datas tab ?


      in my example i want to load only datas from

      category A  03/2016 to 02/2017   

      category B 03/2017 to 02/2018  


      I began by this script,



      [DATAS]:

      LOAD Date(MakeDate([D_YEAR], [D_MONTH]), 'MM_YYYY') as MonthYear,

                      [D_YEAR],

                      [D_MONTH],

          [D_CATEGORIES],

                      [SALES]

      FROM [lib://LOAD_SIM/EXAMPLE 2.xlsx]

      (ooxml, embedded labels, table is DATAS);

       

      [S]:

      LOAD [S_CATEGORIES],

                      [S_YEAR],

                      [BEGIN],

                      [FINISH],

                      [S_SELECT]

      FROM [lib://LOAD_SIM/EXAMPLE 2 BIS.xlsx]

      (ooxml, embedded labels, table is S);

       

       

       

      by i don't know the expression to make the job ....

       

       

      Thank's by advance

       

      Philippe

        • Re: LOADING SELECTED VALUES
          Antonio Mancini

          May be this

           

          LOAD * Inline [
          D_CATEGORIES,D_YEAR,D_MONTH,SALES
          A,2016,2,1000
          A,2016,3,2000
          A,2017,2,2000
          A,2017,5,2000
          B,2017,3,1500]
          ;

          Inner Join
          LOAD DISTINCT S_CATEGORIES as D_CATEGORIES,Year(Start+IterNo()-1) as D_YEAR,Num(Month(Start+IterNo()-1)) as D_MONTH
          While Start+IterNo()-1 <= End;
          LOAD *,MakeDate(S_YEAR,BEGIN) as Start,MakeDate(S_YEAR+1,FINISH) as End
          Inline [
          S_CATEGORIES,S_YEAR,BEGIN,FINISH,S_SELECT
          A,2016,3,2,1
          A,2017,3,2,
          B,2017,3,2,1]

          Where S_SELECT = 1;

            • Re: LOADING SELECTED VALUES
              Philippe BONNIN

              hi Antonio

               

              first, I have to fix an error in my first table (sorry for that) :yellow cells

               

              CORRECT.png

               

              does it change your answer ?

                • Re: LOADING SELECTED VALUES
                  Antonio Mancini

                   

                  LOAD * Inline [
                  D_CATEGORIES,D_YEAR,D_MONTH,SALES
                  A,2016,2,1000
                  A,2016,3,2000
                  A,2017,2,2000
                  A,2017,3,4000
                  A,2017,5,2000
                  B,2017,3,1500]
                  ;

                  Inner Join
                  LOAD DISTINCT S_CATEGORIES as D_CATEGORIES,Year(Start+IterNo()-1) as D_YEAR,Num(Month(Start+IterNo()-1)) as D_MONTH
                  While Start+IterNo()-1 <= End;
                  LOAD *,MakeDate(S_YEAR,BEGIN) as Start,MakeDate(S_YEAR+1,FINISH+1) as End
                  Inline [
                  S_CATEGORIES,S_YEAR,BEGIN,FINISH,S_SELECT
                  A,2016,3,2,1
                  A,2017,3,2,
                  B,2017,3,2,1]

                  Where S_SELECT
                  = 1;

                  • Re: LOADING SELECTED VALUES
                    Philippe BONNIN

                    if not.

                     

                    Should i understand i have to write all these lines for every year in the future ?

                    (LOAD * Inline [
                    D_CATEGORIES,D_YEAR,D_MONTH,SALES
                    A,2016,2,1000
                    A,2016,3,2000
                    A,2017,2,2000
                    A,2017,5,2000
                    B,2017,3,1500]

                     

                    I took a simplified example, but in my real tabs, i have many years and catégories ...variable according to the users

                    so i need a script that makes the job without re-write the datas ... But may be i didn't understood the meaning of the serie of numbers 1000, 2000 ...

                     

                    If it is necessary, i can add a column to concatenate catégories and year (as shown above) .. may be easier to script ? need to eliminate rows where A2017 is present and Keep A2016 & B2017 ?

                     

                    regards

                     

                    Philippe

                      • Re: LOADING SELECTED VALUES
                        Antonio Mancini

                        Philippe,

                        this is my sample data (Inline files)

                        You change to

                        LOAD *

                        From YourTable.;

                        If You want then provide Your Files.

                          • Re: LOADING SELECTED VALUES
                            Philippe BONNIN

                            Very nice

                             

                            i join my file LOADING SIM 2

                             

                            if it's working can you take a look at another thread (without answer) a little more complex

                             

                            SELECTING FORECAST IN LOADING SCRIPT

                             

                             

                            (the same issue but with fiscal year already in the script ... : LOADING SIM)

                             

                            Thank's a lot

                            Philippe

                              • Re: LOADING SELECTED VALUES
                                Antonio Mancini

                                [DATAS]:
                                LOAD Date(MakeDate([D_YEAR], [D_MONTH]), 'MM_YYYY') as MonthYear,
                                [D_YEAR],
                                [D_MONTH],
                                [D_CATEGORIES],
                                [SALES]
                                FROM [lib://LOAD_SIM/EXAMPLE 2.xlsx]
                                (ooxml, embedded labels, table is DATAS);

                                Inner Join
                                LOAD DISTINCT S_CATEGORIES as D_CATEGORIES,Year(Start+IterNo()-1) as D_YEAR,Num(Month(Start+IterNo()-1)) as D_MONTH
                                While Start+IterNo()-1 <= End;
                                LOAD *,MakeDate(S_YEAR,BEGIN) as Start,MakeDate(S_YEAR+1,FINISH+1) as End
                                FROM [lib://LOAD_SIM/EXAMPLE 2 BIS.xlsx]
                                (ooxml, embedded labels, table is S)
                                Where S_SELECT = 1;

                                  • Re: LOADING SELECTED VALUES
                                    Philippe BONNIN

                                    Thank's a lot

                                     

                                    it's seem almost perfect .. except for March 2017 ... i have still the 2 categories ...

                                     

                                    STILL A.png

                                        • Re: LOADING SELECTED VALUES
                                          Philippe BONNIN

                                          So what ?

                                           

                                          The goal is to load CAT B from March 2017 to FEBRUARY 2018

                                           

                                          on March 2017 i don't expect to find CAT A  but only CAT B as well as April , May ....  2017

                                            • Re: LOADING SELECTED VALUES
                                              Antonio Mancini

                                              Remove +1 from FINISH+1                       

                                              [DATAS]:
                                              LOAD Date(MakeDate([D_YEAR], [D_MONTH]), 'MM_YYYY') as MonthYear,
                                              [D_YEAR],
                                              [D_MONTH],
                                              [D_CATEGORIES],
                                              [SALES]
                                              FROM [lib://LOAD_SIM/EXAMPLE 2.xlsx]
                                              (ooxml, embedded labels, table is DATAS);

                                              Inner Join
                                              LOAD DISTINCT S_CATEGORIES as D_CATEGORIES,Year(Start+IterNo()-1) as D_YEAR,Num(Month(Start+IterNo()-1)) as D_MONTH
                                              While Start+IterNo()-1 <= End;
                                              LOAD *,MakeDate(S_YEAR,BEGIN) as Start,MakeDate(S_YEAR+1,FINISH) as End
                                              FROM [lib://LOAD_SIM/EXAMPLE 2 BIS.xlsx]
                                              (ooxml, embedded labels, table is S)
                                              Where S_SELECT = 1;

                                                • Re: LOADING SELECTED VALUES
                                                  Philippe BONNIN

                                                  Great job Antonio !

                                                   

                                                  So let me well understand

                                                   

                                                  first i load the datas tab with

                                                   

                                                  DATAS]:

                                                  LOAD Date(MakeDate([D_YEAR], [D_MONTH]), 'MM_YYYY') as MonthYear,

                                                  [D_YEAR],

                                                  [D_MONTH],

                                                  [D_CATEGORIES],

                                                  [SALES]

                                                  FROM [lib://LOAD_SIM/EXAMPLE 2.xlsx]

                                                  (ooxml, embedded labels, table is DATAS);


                                                  then, i select only the CAT i want to be loaded in my datas tab using ma secondary tab with

                                                   

                                                   

                                                  Inner Join

                                                  LOAD DISTINCT S_CATEGORIES as D_CATEGORIES,Year(Start+IterNo()-1) as D_YEAR,Num(Month(Start+IterNo()-1)) as D_MONTH

                                                  While Start+IterNo()-1 <= End;

                                                  LOAD *,MakeDate(S_YEAR,BEGIN) as Start,MakeDate(S_YEAR+1,FINISH) as End

                                                  FROM [lib://LOAD_SIM/EXAMPLE 2 BIS.xlsx]

                                                  (ooxml, embedded labels, table is S)

                                                  Where S_SELECT = 1;

                                                   

                                                   

                                                  Finally, the secondary is not loaded , just use to select

                                                   

                                                  Right ?

                              • Re: LOADING SELECTED VALUES
                                Antonio Mancini

                                "Where S_SELECT = 1" set to only Row affected

                                Inner Join reduce table DATAS to Expanded Rows ob 2nd table.

                                  • Re: LOADING SELECTED VALUES
                                    Philippe BONNIN

                                    Got it

                                     

                                    Last question to resolve my other issue by myself :

                                     

                                    If i have an other tab to load like this :

                                     

                                    DATAS]:

                                    LOAD Date(MakeDate([D_YEAR], [D_MONTH]), 'MM_YYYY') as MonthYear,

                                    [D_YEAR],

                                    [D_MONTH],

                                    [D_CATEGORIES],

                                    [SALES]

                                    FROM [lib://LOAD_SIM/EXAMPLE 2.xlsx]

                                    (ooxml, embedded labels, table is DATAS);

                                     

                                    DATAS2]:

                                    LOAD Date(MakeDate([D_YEAR], [D_MONTH]), 'MM_YYYY') as MonthYear,

                                    [G_YEAR],

                                    [G_MONTH],

                                    [G_CATEGORIES],

                                    [G_GOAL_SALES]

                                    FROM [lib://LOAD_SIM/EXAMPLE 2.xlsx]

                                    (ooxml, embedded labels, table is DATAS2);

                                     

                                    If i want to apply the same selection in my 2 tables like before, what is the good syntax ?

                                     

                                    Some thing like this ?

                                     

                                    Inner Join

                                    LOAD DISTINCT S_CATEGORIES as D_CATEGORIES, S_CATEGORIES as G_CATEGORIES, Year(Start+IterNo()-1) as D_YEAR,Num(Month(Start+IterNo()-1)) as D_MONTH

                                    While Start+IterNo()-1 <= End;

                                    LOAD *,MakeDate(S_YEAR,BEGIN) as Start,MakeDate(S_YEAR+1,FINISH) as End

                                    FROM [lib://LOAD_SIM/EXAMPLE 2 BIS.xlsx]

                                    (ooxml, embedded labels, table is S)

                                    Where S_SELECT = 1;

                                     

                                    Or  repeat the inner Join after each tab ?

                                     

                                      • Re: LOADING SELECTED VALUES
                                        Antonio Mancini

                                        Concatenate two tables by changing Fields name, like G_YEAR as D_YEAR,G_MONTH as D_MONTH,....,

                                        then Inner Join

                                          • Re: LOADING SELECTED VALUES
                                            Philippe BONNIN

                                            Hi Antonio

                                             

                                            I tried

                                             

                                            [DATAS]:

                                            LOAD Date(MakeDate([D_YEAR], [D_MONTH]), 'MM_YYYY') as MonthYear,

                                            [D_YEAR],

                                            [D_MONTH],

                                            [D_CATEGORIES],

                                            [SALES]

                                            FROM [lib://LOAD_SIM/EXAMPLE 2.xlsx]

                                            (ooxml, embedded labels, table is DATAS);

                                             

                                            DATAS2]:

                                            LOAD Date(MakeDate([D_YEAR], [D_MONTH]), 'MM_YYYY') as MonthYear,

                                            [G_YEAR] As [D_YEAR],

                                            [G_MONTH] As [D_MONTH],

                                            [G_CATEGORIES] As [D_CATEGORIES],

                                            [G_GOAL_SALES]

                                            FROM [lib://LOAD_SIM/EXAMPLE 2.xlsx]

                                            (ooxml, embedded labels, table is DATAS2);

                                             

                                            Inner Join

                                            LOAD DISTINCT S_CATEGORIES as D_CATEGORIES,Year(Start+IterNo()-1) as D_YEAR,Num(Month(Start+IterNo()-1)) as D_MONTH

                                            While Start+IterNo()-1 <= End;

                                            LOAD *,MakeDate(S_YEAR,BEGIN) as Start,MakeDate(S_YEAR+1,FINISH) as End

                                            FROM [lib://LOAD_SIM/EXAMPLE 2 BIS.xlsx]

                                            (ooxml, embedded labels, table is S)

                                            Where S_SELECT = 1;

                                             

                                            But i got


                                            error.png



                                            apart from that


                                            is it compatible with          LOAD (My date(MakeDate([D_YEAR],…. ?


                                            regards


                                            Philippe


                                              • Re: LOADING SELECTED VALUES
                                                Antonio Mancini

                                                Yes, You don't have D_YEAR in "EXEMPLE 2 BIS".

                                                I'm sorry, I don't understand Your new request.

                                                Can You provide Your Tables, and expected result ?

                                                  • Re: LOADING SELECTED VALUES
                                                    Philippe BONNIN

                                                    But i just copied  your script and completed with DATAS2??

                                                     

                                                    [DATAS]:

                                                    LOAD Date(MakeDate([D_YEAR], [D_MONTH]), 'MM_YYYY') as MonthYear,

                                                    [D_YEAR],

                                                    [D_MONTH],

                                                    [D_CATEGORIES],

                                                    [SALES]

                                                    FROM [lib://LOAD_SIM/EXAMPLE 2.xlsx]

                                                    (ooxml, embedded labels, table is DATAS);

                                                     

                                                    [DATAS2]:

                                                    LOAD Date(MakeDate([D_YEAR], [D_MONTH]), 'MM_YYYY') as MonthYear,

                                                    [G_YEAR] As [D_YEAR],

                                                    [G_MONTH] As [D_MONTH],

                                                    [G_CATEGORIES] As [D_CATEGORIES],

                                                    [G_GOAL_SALES]

                                                    FROM [lib://LOAD_SIM/EXAMPLE 3.xlsx]

                                                    (ooxml, embedded labels, table is DATAS2);

                                                     

                                                    Inner Join

                                                    LOAD DISTINCT S_CATEGORIES as D_CATEGORIES,Year(Start+IterNo()-1) as D_YEAR,Num(Month(Start+IterNo()-1)) as D_MONTH

                                                    While Start+IterNo()-1 <= End;

                                                    LOAD *,MakeDate(S_YEAR,BEGIN) as Start,MakeDate(S_YEAR+1,FINISH) as End

                                                    FROM [lib://LOAD_SIM/EXAMPLE 2 BIS.xlsx]

                                                    (ooxml, embedded labels, table is S)

                                                    Where S_SELECT = 1;

                                                     

                                                    Can you help me please ?

                                                      • Re: LOADING SELECTED VALUES
                                                        Antonio Mancini

                                                        This is Your above script

                                                        DATAS2]:

                                                        LOAD Date(MakeDate([D_YEAR], [D_MONTH]), 'MM_YYYY') as MonthYear,

                                                        [G_YEAR] As [D_YEAR],

                                                        [G_MONTH] As [D_MONTH],

                                                        [G_CATEGORIES] As [D_CATEGORIES],

                                                        [G_GOAL_SALES]

                                                        FROM [lib://LOAD_SIM/EXAMPLE 2.xlsx]

                                                        (ooxml, embedded labels, table is DATAS2);

                                                        Please, provide Your Data Sample (Table) and Your expected result.

                                                        Regards,

                                                        Antonio

                                                            • Re: LOADING SELECTED VALUES
                                                              Antonio Mancini

                                                              Do You need 1 or 2 tables, SALES and G_GOAL_SALES as Sum or Distinct Values ?

                                                              Ca You provide Your expected result ?

                                                                • Re: LOADING SELECTED VALUES
                                                                  Philippe BONNIN

                                                                  Hi Antonio

                                                                   

                                                                   

                                                                  To keep  2 tables is good for me;

                                                                   

                                                                  For the final résult, let me explain with another app. that's working pretty good (I had chosen the actual example to focus on my issue of loading script by i see it's more difficult to understand)

                                                                   

                                                                  i want graph or KPI that can be able to compare CURRENT YEAR with previous year goal values like this …

                                                                  VIZU 1.png

                                                                  VIZU 2.png

                                                                   

                                                                   

                                                                  so as you could see in the script below ,in 2 tables (CA_R$ & FP_R$) I have current values and 2 other tab (CA_P$ & FP_P$) I have goal values (forecast  for sales and costs)

                                                                   

                                                                  and it is in these 2 last tables that the issue of multiple forecast (similar of categories in my example)

                                                                   

                                                                  appears (sometimes, goal values accumulate from several forecast per year …)


                                                                  FPP.png

                                                                  VMP.png


                                                                   

                                                                  So i want to load a new table to select only one forecast  per year in my 2 tables, but keep my 2 tables distinct


                                                                  SELECTION.png


                                                                  .... and don’t make troubles in my AsofTable , and Master calendar …,As you can see i my actual loading script below

                                                                   

                                                                   

                                                                  [CA_R$]:  # current values

                                                                  LOAD Date(MakeDate([VMS_ANN], [VMS_MOI]), 'MM_YYYY') as MonthYear,

                                                                                  [VMS_ANN],

                                                                                  [VMS_MOI],

                                                                      [VMS_NCL] AS [NCL-VMS_NCL],

                                                                      [VMS_CATTC],

                                                                      [VMS_TVA],

                                                                                  [VMS_ACH],

                                                                                  [VMS_TXACH],

                                                                                  [VMS_MEAC],

                                                                                  [VMS_MEAD],

                                                                                  [VMS_CONAFF],

                                                                                  [VMS_STKFIN],

                                                                                  [VMS_STKINI],

                                                                                  [VMS_DEMACH],

                                                                                  [VMS_DEMVTE],

                                                                                  [VMS_DEMINC],

                                                                                  [VMS_CAHTMGV],

                                                                                  [VMS_MBTVTE],

                                                                                  [VMS_CAHTMGE],

                                                                  [CA_P$]:  # goal values

                                                                  LOAD Date(MakeDate([VMP_ANN], [VMP_MOI]), 'MM_YYYY') as MonthYear,

                                                                                 

                                                                      [VMP_ANN],

                                                                      [VMP_MOI],

                                                                      [VMP_NCL] AS [NCL-VMS_NCL],

                                                                      [VMP_CDSIMP],

                                                                      [VMP_TVA],

                                                                                  [VMP_ACHCONS],

                                                                                  [VMP_TXACH],

                                                                                  [VMP_TXTXACH],

                                                                                  [VMP_MEAC],

                                                                                  [VMP_TXMEAC],

                                                                                  [VMP_MEAD],

                                                                                  [VMP_TXMEAD],

                                                                                  [VMP_CONAFF],


                                                                  [FP_P$]: #goal values

                                                                  LOAD Date(MakeDate([FPP_ANN], [FPP_MOI]), 'MM_YYYY') as MonthYear,

                                                                      [FPP_ANN],

                                                                      [FPP_MOI],

                                                                      [FPP_NCL] AS [NCL-VMS_NCL],

                                                                      [FPP_CDSIMP],

                                                                      [FPP_TYPV],

                                                                                  [FPP_HRS],

                                                                                  [FPP_VHT],

                                                                                  [FPP_MTFP],

                                                                                  [FPP_COUHR],

                                                                                  [FPP_PFPCAHT],

                                                                                  [FPP_TXVULN]   

                                                                                   FROM [lib://MASTER2/PDFPP.xls]

                                                                  (biff, embedded labels, table is FP_P$)

                                                                  WHERE EXISTS ([NCL-VMS_NCL], [FPP_NCL]);



                                                                  Then

                                                                   

                                                                  AsOfTable:

                                                                  LOAD MonthYear as AsOfMonthYear,

                                                                  MonthYear,

                                                                       'CY' as Flag

                                                                  Resident [CA_R$];

                                                                   

                                                                  Concatenate (AsOfTable)

                                                                  LOAD MonthYear as AsOfMonthYear,

                                                                  Date(AddYears(MonthYear, -1), 'MM_YYYY') as MonthYear,

                                                                       'PY' as Flag

                                                                  Resident [CA_R$];

                                                                   

                                                                  Concatenate (AsOfTable)

                                                                  LOAD MonthYear as AsOfMonthYear,

                                                                  MonthYear,

                                                                       'Goal' as Flag

                                                                  Resident [CA_P$];

                                                                   

                                                                  And then

                                                                   

                                                                  Set vFM = 1;  # set month beginning fiscal year

                                                                   

                                                                  QuartersMap: 

                                                                  MAPPING LOAD  

                                                                  rowno() as Month, 

                                                                  'Q' & Ceil (rowno()/3) as Quarter 

                                                                  AUTOGENERATE (12); 

                                                                   

                                                                  Temp: 

                                                                  Load 

                                                                                 min(AsOfMonthYear) as minDate, 

                                                                                 max(AsOfMonthYear) as maxDate 

                                                                  Resident AsOfTable; 

                                                                   

                                                                  Let varMinDate = Num(Peek('minDate', 0, 'Temp'));

                                                                  Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));

                                                                  DROP Table Temp; 

                                                                   

                                                                  TempCalendar: 

                                                                  LOAD 

                                                                                 $(varMinDate) + Iterno()-1 As Num, 

                                                                                 Date($(varMinDate) + IterNo() - 1) as TempDate 

                                                                                 AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate); 

                                                                   

                                                                  MasterCalendar: 

                                                                  Load Dual(fYear-1 &'/'& fYear, fYear) as FYear,          // Dual fiscal year

                                                                           Dual(Month, fMonth)                as FMonth,           // Dual fiscal month

                                                                            *;

                                                                   

                                                                  LOAD Year + If(Month >= $(vFM), 1, 0) as fYear, // Numeric fiscal year

                                                                       Mod(Month-$(vFM), 12)+1        as fMonth,          // Numeric fiscal month

                                                                       *;

                                                                  Load 

                                                                                 TempDate AS AsOfMonthYear, 

                                                                                 week(TempDate) As Week, 

                                                                                 Year(TempDate) As Year, 

                                                                                 Month(TempDate) As Month, 

                                                                                 Day(TempDate) As Day, 

                                                                                 YeartoDate(TempDate)*-1 as CurYTDFlag, 

                                                                                 YeartoDate(TempDate,-1)*-1 as LastYTDFlag, 

                                                                                 inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12, 

                                                                  //                date(monthstart(TempDate), 'MMM-YYYY') as MonthYear, 

                                                                                 ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter, 

                                                                                 Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,

                                                                                 WeekDay(TempDate) as WeekDay 

                                                                  Resident TempCalendar 

                                                                  Order By TempDate ASC; 

                                                                  Drop Table TempCalendar; 

                                                                   

                                                                   

                                                                  I hope it will be understandable

                                                                   

                                                                  best regards

                                                                   

                                                                  Philippe

                                                                  • Re: LOADING SELECTED VALUES
                                                                    Antonio Mancini

                                                                    Try this

                                                                    [DATAS]:
                                                                    LOAD Date(MakeDate([D_YEAR], [D_MONTH]), 'MM_YYYY') as MonthYear,
                                                                    [D_YEAR],
                                                                    [D_MONTH],
                                                                    [D_CATEGORIES],
                                                                    [SALES]
                                                                    FROM [lib://LOAD_SIM/EXAMPLE 2.xlsx]
                                                                    (ooxml, embedded labels, table is DATAS);
                                                                    Inner Join (DATAS)
                                                                    LOAD DISTINCT S_CATEGORIES as D_CATEGORIES,Year(Start+IterNo()-1) as D_YEAR,Num(Month(Start+IterNo()-1)) as D_MONTH
                                                                    While Start+IterNo()-1 <= End;
                                                                    LOAD *,MakeDate(S_YEAR,BEGIN) as Start,MakeDate(S_YEAR+1,FINISH) as End
                                                                    FROM [lib://LOAD_SIM/EXAMPLE 2 BIS.xlsx]
                                                                    (ooxml, embedded labels, table is S)
                                                                    Where S_SELECT = 1;

                                                                    [DATAS2]:
                                                                    LOAD Date(MakeDate([G_YEAR], [G_MONTH]), 'MM_YYYY') as MonthYear,
                                                                    [G_YEAR],
                                                                    [G_MONTH],
                                                                    [G_CATEGORIES],
                                                                    [G_GOAL_SALES]
                                                                    FROM [lib://LOAD_SIM/EXAMPLE 3.xlsx]
                                                                    (ooxml, embedded labels, table is DATAS2);
                                                                    Inner Join (DATAS2)
                                                                    LOAD DISTINCT S_CATEGORIES as G_CATEGORIES,Year(Start+IterNo()-1) as G_YEAR,Num(Month(Start+IterNo()-1)) as G_MONTH
                                                                    While Start+IterNo()-1 <= End;
                                                                    LOAD *,MakeDate(S_YEAR,BEGIN) as Start,MakeDate(S_YEAR+1,FINISH) as End
                                                                    FROM [lib://LOAD_SIM/EXAMPLE 2 BIS.xlsx]
                                                                    (ooxml, embedded labels, table is
                                                                    S)
                                                                    Where S_SELECT = 1;

                                                                      • Re: LOADING SELECTED VALUES
                                                                        Philippe BONNIN

                                                                        Thank's

                                                                         

                                                                        sure, i'll try as soon as possible for me

                                                                         

                                                                        see you soon

                                                                         

                                                                        Philippe.

                                                                        • Re: LOADING SELECTED VALUES
                                                                          Philippe BONNIN

                                                                          Hi Antonio

                                                                          I'm really disappointed

                                                                          I have strictly transferred your script to my real app.

                                                                          Here are  the following correspondences with my previous example in my script (ANN & MOI replace YEAR & MONTH, and CDSIMP replace CATEGORIES) :

                                                                          CA_P$]:

                                                                          LOAD Date(MakeDate([VMP_ANN], [VMP_MOI]), 'MM_YYYY') as MonthYear,

                                                                                        

                                                                          [VMP_ANN],

                                                                          [VMP_MOI],

                                                                          [VMP_NCL] AS [NCL-VMS_NCL],

                                                                          [VMP_CDSIMP],

                                                                          [VMP_TVA],

                                                                           

                                                                                          … …

                                                                                          [VMP_CATTC],

                                                                                          ….  …

                                                                           

                                                                          FROM [lib://MAGLAND/PDVMP.xls]

                                                                          (biff, embedded labels, table is CA_P$)

                                                                          WHERE EXISTS ([NCL-VMS_NCL], [VMP_NCL]);

                                                                          Inner Join (CA_P$)

                                                                          LOAD DISTINCT SIP_CDSIMP as VMP_CDSIMP,Year(Start+IterNo()-1) as VMP_ANN,Num(Month(Start+IterNo()-1)) as VMP_MOI

                                                                          While Start+IterNo()-1 <= End;

                                                                          LOAD *,MakeDate(SIP_EXE,SIP_BEGIN) as Start,MakeDate(SIP_EXE+1,SIP_FINISH) as End

                                                                          FROM [lib://MAGLAND/PRSIMP.xls]

                                                                          (biff, embedded labels, table is SIMP$)

                                                                          Where SIP_SEL = 1;

                                                                          [FP_P$]:

                                                                          LOAD Date(MakeDate([FPP_ANN], [FPP_MOI]), 'MM_YYYY') as MonthYear,

                                                                                        

                                                                          [FPP_ANN],

                                                                          [FPP_MOI],

                                                                          [FPP_NCL] AS [NCL-VMS_NCL],

                                                                          [FPP_CDSIMP],

                                                                          [FPP_TYPV],

                                                                                          [FPP_HRS],

                                                                                          … …

                                                                                          [FPP_MTFP],

                                                                                        

                                                                                        

                                                                          FROM [lib://MAGLAND/PDFPP.xls]

                                                                          (biff, embedded labels, table is FP_P$)

                                                                          WHERE EXISTS ([NCL-VMS_NCL], [FPP_NCL]);

                                                                          Inner Join (FP_P$)

                                                                          LOAD DISTINCT SIP_CDSIMP as FPP_CDSIMP,Year(Start+IterNo()-1) as FPP_ANN,Num(Month(Start+IterNo()-1)) as FPP_MOI

                                                                          While Start+IterNo()-1 <= End;

                                                                          LOAD *,MakeDate(SIP_EXE,SIP_BEGIN) as Start,MakeDate(SIP_EXE+1,SIP_FINISH) as End

                                                                          FROM [lib://MAGLAND/PRSIMP.xls]

                                                                          (biff, embedded labels, table is SIMP$)

                                                                          Where SIP_SEL = 1;

                                                                           

                                                                          An the selecting table

                                                                          SELECTION MAGLAND.png

                                                                           

                                                                          Here is the result !

                                                                          MAGLANG RESULT.png

                                                                           

                                                                           

                                                                           

                                                                          I do not understand where I made the mistake ; i wrote exactly the same innerjoin tab with different names …

                                                                           

                                                                          i join my app , if you want to take a look…

                                                                           

                                                                          Thank’s by advance

                                                                           

                                                                          Philippe