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
Hi Antonio
To keep 2 tables is good for me;
For the final résult, let me explain with another app. that's working pretty good (I had chosen the actual example to focus on my issue of loading script by i see it's more difficult to understand)
i want graph or KPI that can be able to compare CURRENT YEAR with previous year goal values like this …
so as you could see in the script below ,in 2 tables (CA_R$ & FP_R$) I have current values and 2 other tab (CA_P$ & FP_P$) I have goal values (forecast for sales and costs)
and it is in these 2 last tables that the issue of multiple forecast (similar of categories in my example)
appears (sometimes, goal values accumulate from several forecast per year …)
So i want to load a new table to select only one forecast per year in my 2 tables, but keep my 2 tables distinct …
.... and don’t make troubles in my AsofTable , and Master calendar …,As you can see i my actual loading script below
…
[CA_R$]: # current values
LOAD Date(MakeDate([VMS_ANN], [VMS_MOI]), 'MM_YYYY') as MonthYear,
[VMS_ANN],
[VMS_MOI],
[VMS_NCL] AS [NCL-VMS_NCL],
[VMS_CATTC],
[VMS_TVA],
[VMS_ACH],
[VMS_TXACH],
[VMS_MEAC],
[VMS_MEAD],
[VMS_CONAFF],
[VMS_STKFIN],
[VMS_STKINI],
[VMS_DEMACH],
[VMS_DEMVTE],
[VMS_DEMINC],
[VMS_CAHTMGV],
[VMS_MBTVTE],
[VMS_CAHTMGE],
…
[CA_P$]: # goal values
LOAD Date(MakeDate([VMP_ANN], [VMP_MOI]), 'MM_YYYY') as MonthYear,
[VMP_ANN],
[VMP_MOI],
[VMP_NCL] AS [NCL-VMS_NCL],
[VMP_CDSIMP],
[VMP_TVA],
[VMP_ACHCONS],
[VMP_TXACH],
[VMP_TXTXACH],
[VMP_MEAC],
[VMP_TXMEAC],
[VMP_MEAD],
[VMP_TXMEAD],
[VMP_CONAFF],
…
[FP_P$]: #goal values
LOAD Date(MakeDate([FPP_ANN], [FPP_MOI]), 'MM_YYYY') as MonthYear,
[FPP_ANN],
[FPP_MOI],
[FPP_NCL] AS [NCL-VMS_NCL],
[FPP_CDSIMP],
[FPP_TYPV],
[FPP_HRS],
[FPP_VHT],
[FPP_MTFP],
[FPP_COUHR],
[FPP_PFPCAHT],
[FPP_TXVULN]
FROM [lib://MASTER2/PDFPP.xls]
(biff, embedded labels, table is FP_P$)
WHERE EXISTS ([NCL-VMS_NCL], [FPP_NCL]);
Then
AsOfTable:
LOAD MonthYear as AsOfMonthYear,
MonthYear,
'CY' as Flag
Resident [CA_R$];
Concatenate (AsOfTable)
LOAD MonthYear as AsOfMonthYear,
Date(AddYears(MonthYear, -1), 'MM_YYYY') as MonthYear,
'PY' as Flag
Resident [CA_R$];
Concatenate (AsOfTable)
LOAD MonthYear as AsOfMonthYear,
MonthYear,
'Goal' as Flag
Resident [CA_P$];
And then
Set vFM = 1; # set month beginning fiscal year
QuartersMap:
MAPPING LOAD
rowno() as Month,
'Q' & Ceil (rowno()/3) as Quarter
AUTOGENERATE (12);
Temp:
Load
min(AsOfMonthYear) as minDate,
max(AsOfMonthYear) as maxDate
Resident AsOfTable;
Let varMinDate = Num(Peek('minDate', 0, 'Temp'));
Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));
DROP Table Temp;
TempCalendar:
LOAD
$(varMinDate) + Iterno()-1 As Num,
Date($(varMinDate) + IterNo() - 1) as TempDate
AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);
MasterCalendar:
Load Dual(fYear-1 &'/'& fYear, fYear) as FYear, // Dual fiscal year
Dual(Month, fMonth) as FMonth, // Dual fiscal month
*;
LOAD Year + If(Month >= $(vFM), 1, 0) as fYear, // Numeric fiscal year
Mod(Month-$(vFM), 12)+1 as fMonth, // Numeric fiscal month
*;
Load
TempDate AS AsOfMonthYear,
week(TempDate) As Week,
Year(TempDate) As Year,
Month(TempDate) As Month,
Day(TempDate) As Day,
YeartoDate(TempDate)*-1 as CurYTDFlag,
YeartoDate(TempDate,-1)*-1 as LastYTDFlag,
inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12,
// date(monthstart(TempDate), 'MMM-YYYY') as MonthYear,
ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,
Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,
WeekDay(TempDate) as WeekDay
Resident TempCalendar
Order By TempDate ASC;
Drop Table TempCalendar;
I hope it will be understandable
best regards
Philippe
Hi Antonio
I'm really disappointed
I have strictly transferred your script to my real app.
Here are the following correspondences with my previous example in my script (ANN & MOI replace YEAR & MONTH, and CDSIMP replace CATEGORIES) :
CA_P$]:
LOAD Date(MakeDate([VMP_ANN], [VMP_MOI]), 'MM_YYYY') as MonthYear,
[VMP_ANN],
[VMP_MOI],
[VMP_NCL] AS [NCL-VMS_NCL],
[VMP_CDSIMP],
[VMP_TVA],
… …
[VMP_CATTC],
…. …
FROM [lib://MAGLAND/PDVMP.xls]
(biff, embedded labels, table is CA_P$)
WHERE EXISTS ([NCL-VMS_NCL], [VMP_NCL]);
Inner Join (CA_P$)
LOAD DISTINCT SIP_CDSIMP as VMP_CDSIMP,Year(Start+IterNo()-1) as VMP_ANN,Num(Month(Start+IterNo()-1)) as VMP_MOI
While Start+IterNo()-1 <= End;
LOAD *,MakeDate(SIP_EXE,SIP_BEGIN) as Start,MakeDate(SIP_EXE+1,SIP_FINISH) as End
FROM [lib://MAGLAND/PRSIMP.xls]
(biff, embedded labels, table is SIMP$)
Where SIP_SEL = 1;
[FP_P$]:
LOAD Date(MakeDate([FPP_ANN], [FPP_MOI]), 'MM_YYYY') as MonthYear,
[FPP_ANN],
[FPP_MOI],
[FPP_NCL] AS [NCL-VMS_NCL],
[FPP_CDSIMP],
[FPP_TYPV],
[FPP_HRS],
… …
[FPP_MTFP],
FROM [lib://MAGLAND/PDFPP.xls]
(biff, embedded labels, table is FP_P$)
WHERE EXISTS ([NCL-VMS_NCL], [FPP_NCL]);
Inner Join (FP_P$)
LOAD DISTINCT SIP_CDSIMP as FPP_CDSIMP,Year(Start+IterNo()-1) as FPP_ANN,Num(Month(Start+IterNo()-1)) as FPP_MOI
While Start+IterNo()-1 <= End;
LOAD *,MakeDate(SIP_EXE,SIP_BEGIN) as Start,MakeDate(SIP_EXE+1,SIP_FINISH) as End
FROM [lib://MAGLAND/PRSIMP.xls]
(biff, embedded labels, table is SIMP$)
Where SIP_SEL = 1;
An the selecting table
Here is the result !
I do not understand where I made the mistake ; i wrote exactly the same innerjoin tab with different names …
i join my app , if you want to take a look…
Thank’s by advance
Philippe
