topic Re: SELECTING FORECAST IN LOADING SCRIPT in QlikView

SELECTING FORECAST IN LOADING SCRIPT

philgood34 — Sat, 14 Oct 2017 17:45:43 GMT

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

Re: SELECTING FORECAST IN LOADING SCRIPT

its_anandrjs — Sat, 14 Oct 2017 18:08:22 GMT

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

philgood34 — Sat, 14 Oct 2017 19:09:59 GMT

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

Re: SELECTING FORECAST IN LOADING SCRIPT

its_anandrjs — Sat, 14 Oct 2017 19:44:25 GMT

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

philgood34 — Sat, 14 Oct 2017 20:37:27 GMT

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 ?

Re: SELECTING FORECAST IN LOADING SCRIPT

its_anandrjs — Sat, 14 Oct 2017 20:41:53 GMT

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

philgood34 — Sat, 14 Oct 2017 20:58:38 GMT

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