Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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;
hi Antonio
first, I have to fix an error in my first table (sorry for that) :yellow cells
does it change your answer ?
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;
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
Philippe,
this is my sample data (Inline files)
You change to
LOAD *
From YourTable.;
If You want then provide Your Files.
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
[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;
Thank's a lot
it's seem almost perfect .. except for March 2017 ... i have still the 2 categories ...