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
So what ?
The goal is to load CAT B from March 2017 to FEBRUARY 2018
on March 2017 i don't expect to find CAT A but only CAT B as well as April , May .... 2017
Remove +1 from FINISH+1
[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) as End
FROM [lib://LOAD_SIM/EXAMPLE 2 BIS.xlsx]
(ooxml, embedded labels, table is S)
Where S_SELECT = 1;
Great job Antonio !
So let me well understand
first i load the datas tab with
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);
then, i select only the CAT i want to be loaded in my datas tab using ma secondary tab with
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
FROM [lib://LOAD_SIM/EXAMPLE 2 BIS.xlsx]
(ooxml, embedded labels, table is S)
Where S_SELECT = 1;
Finally, the secondary is not loaded , just use to select
Right ?
"Where S_SELECT = 1" set to only Row affected
Inner Join reduce table DATAS to Expanded Rows ob 2nd table.
Got it
Last question to resolve my other issue by myself :
If i have an other tab to load like 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);
DATAS2]:
LOAD Date(MakeDate([D_YEAR], [D_MONTH]), 'MM_YYYY') as MonthYear,
[G_YEAR],
[G_MONTH],
[G_CATEGORIES],
[G_GOAL_SALES]
FROM [lib://LOAD_SIM/EXAMPLE 2.xlsx]
(ooxml, embedded labels, table is DATAS2);
If i want to apply the same selection in my 2 tables like before, what is the good syntax ?
Some thing like this ?
Inner Join
LOAD DISTINCT S_CATEGORIES as D_CATEGORIES, S_CATEGORIES as G_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;
Or repeat the inner Join after each tab ?
Concatenate two tables by changing Fields name, like G_YEAR as D_YEAR,G_MONTH as D_MONTH,....,
then Inner Join
Hi Antonio
I tried
[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);
DATAS2]:
LOAD Date(MakeDate([D_YEAR], [D_MONTH]), 'MM_YYYY') as MonthYear,
[G_YEAR] As [D_YEAR],
[G_MONTH] As [D_MONTH],
[G_CATEGORIES] As [D_CATEGORIES],
[G_GOAL_SALES]
FROM [lib://LOAD_SIM/EXAMPLE 2.xlsx]
(ooxml, embedded labels, table is DATAS2);
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
FROM [lib://LOAD_SIM/EXAMPLE 2 BIS.xlsx]
(ooxml, embedded labels, table is S)
Where S_SELECT = 1;
But i got
apart from that
is it compatible with LOAD (My date(MakeDate([D_YEAR],…. ?
regards
Philippe
Yes, You don't have D_YEAR in "EXEMPLE 2 BIS".
I'm sorry, I don't understand Your new request.
Can You provide Your Tables, and expected result ?
But i just copied your script and completed with DATAS2??
[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);
[DATAS2]:
LOAD Date(MakeDate([D_YEAR], [D_MONTH]), 'MM_YYYY') as MonthYear,
[G_YEAR] As [D_YEAR],
[G_MONTH] As [D_MONTH],
[G_CATEGORIES] As [D_CATEGORIES],
[G_GOAL_SALES]
FROM [lib://LOAD_SIM/EXAMPLE 3.xlsx]
(ooxml, embedded labels, table is DATAS2);
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
FROM [lib://LOAD_SIM/EXAMPLE 2 BIS.xlsx]
(ooxml, embedded labels, table is S)
Where S_SELECT = 1;
Can you help me please ?
This is Your above script
DATAS2]:
LOAD Date(MakeDate([D_YEAR], [D_MONTH]), 'MM_YYYY') as MonthYear,
[G_YEAR] As [D_YEAR],
[G_MONTH] As [D_MONTH],
[G_CATEGORIES] As [D_CATEGORIES],
[G_GOAL_SALES]
FROM [lib://LOAD_SIM/EXAMPLE 2.xlsx]
(ooxml, embedded labels, table is DATAS2);
Please, provide Your Data Sample (Table) and Your expected result.
Regards,
Antonio