SELECTING FORECAST IN LOADING SCRIPT

[philgood34]

Hi

i have 3 TAB :

first and second are CURRENT AND FORECAST sales and customers

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

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

[its_anandrjs]

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;

[philgood34]

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)

Regards

Philippe

[its_anandrjs]

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] );

[philgood34]

Are you sure ?

the first "where[select] = 1 loads

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 :

Any other idea ?

[its_anandrjs]

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] );

[philgood34]

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