Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
philgood34
Contributor II

SELECTING FORECAST IN LOADING SCRIPT

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


6 Replies

Re: SELECTING FORECAST IN LOADING SCRIPT

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
Contributor II

Re: SELECTING FORECAST IN LOADING SCRIPT

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

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
Contributor II

Re: SELECTING FORECAST IN LOADING SCRIPT

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

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
Contributor II

Re: SELECTING FORECAST IN LOADING SCRIPT

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

Community Browser