LOADING SELECTED VALUES

[philgood34]

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

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

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

:

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

[antoniotiman]

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;

[philgood34]

hi Antonio

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

does it change your answer ?

[antoniotiman]

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;

[philgood34]

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

[antoniotiman]

Philippe,

this is my sample data (Inline files)

You change to

LOAD *

From YourTable.;

If You want then provide Your Files.

[philgood34]

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

[antoniotiman]

[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;

[philgood34]

Thank's a lot

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

[antoniotiman]