Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I need to load a table but with a where condition.
I have a field called %KEY_BUDGET.
This field is like this : Bazoches_200801
Bazoches_200802
Bazoches_200803
Bazoches_200804
....
Bazoches_201601
Bazoches_201602
Bazoches_201603
Bazoches_201604
Bazoches is a name of Park, 2016 is the years and 01 is the mounth.
I need to take just the rows where the year = 2016.
Someone can help me?
Regards
Pasquale
May be this:
Where WildMatch(%KEY_BUDGET, '*2016*');
load * ,
%key
..
........from database;
Where %key='park' and year=2016
you can use the wildmatch function
example:
where wildMatch(%KEY_BUDGET,'*2016*')>0
thanks for the previous response, but now I have another problem...
script:
Map_Day_Per_Month:
Mapping LOAD * Inline [
Month, NDay
Jan, 31
Feb, 28
Mar, 31
Apr,30
May,31
Jun,31
Jul,30
Aug,31
Sep,30
Oct,30
Nov,30
Dec,31
];
BudgetYtD:
NoConcatenate
load 'Bazoches_'&Year&'_'&Month as %KEY_BDG_YTD,
[p50 Produktion Bazoches] as BU_P50_PROD,
ApplyMap('Map_Day_Per_Month',SubField(%KEY_BDG_YTD,'_',3)) as DayPerMonth
Resident tmp
Where Year = '2016';
I have a problem with ApplyMap.
%KEY_BDG_YTD is like this format : Bazoches_2016_Jan (exemple)
Someone can help me?
Thanks Pasquale
[p50 Produktion Bazoches] as BU_P50_PROD
should be 'p50 Production Bazoches' as BU_P50_PROD
and you need to add Mapping for the load Inline
What about this:
Map_Day_Per_Month:
Mapping LOAD * Inline [
Month, NDay
Jan, 31
Feb, 28
Mar, 31
Apr,30
May,31
Jun,31
Jul,30
Aug,31
Sep,30
Oct,30
Nov,30
Dec,31
];
BudgetYtD:
NoConcatenate
LOAD 'Bazoches_'&Year&'_'&Month as %KEY_BDG_YTD,
[p50 Produktion Bazoches] as BU_P50_PROD,
ApplyMap('Map_Day_Per_Month', Right(%KEY_BDG_YTD, 3)) as DayPerMonth
Resident tmp
Where Year = '2016';
or
BudgetYtD:
NoConcatenate
LOAD 'Bazoches_'&Year&'_'&Month as %KEY_BDG_YTD,
[p50 Produktion Bazoches] as BU_P50_PROD,
ApplyMap('Map_Day_Per_Month', SubField(%KEY_BDG_YTD, '_', -1)) as DayPerMonth
Resident tmp
Where Year = '2016';
In my BudgetYtD Table I have 2 column. %KEY_BDG_YTD and BU_P50_PROD.
Now I need to add another column in this table (the numbero of the days for each months).
For this cause I create a Map_Day_Per_Month table (Load * inline)
The Name of the mouth is in the field %KEY_BDG_YTD .
So now I have
%KEY_BDG_YTD BU_P50_PROD
Bazoches_2016_Jan 5000
What I want
%KEY_BDG_YTD BU_P50_PROD DayPerMonth
Bazoches_2016_Jan 5000 31
Bazoches_2016_Feb 4847 28
I hope that you can Understand..
Regards
Pasquale
Both the above scripts seems to work for me
Sample Script:
Map_Day_Per_Month:
Mapping LOAD * Inline [
Month, NDay
Jan, 31
Feb, 28
Mar, 31
Apr,30
May,31
Jun,31
Jul,30
Aug,31
Sep,30
Oct,30
Nov,30
Dec,31
];
BudgetYtD:
LOAD *,
ApplyMap('Map_Day_Per_Month', SubField(%KEY_BDG_YTD, '_', -1)) as DayPerMonth1,
ApplyMap('Map_Day_Per_Month', Right(%KEY_BDG_YTD, 3)) as DayPerMonth2;
LOAD * Inline [
%KEY_BDG_YTD, BU_P50_PROD
Bazoches_2016_Jan, 5000
Bazoches_2016_Feb, 4847
];