Take just a part of field for a where condition

[pascos88]

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

[sunny_talwar · Accepted solution]

May be this:

Where WildMatch(%KEY_BUDGET, '*2016*');

[Chanty4u]

load * ,

%key

..

........from database;

Where %key='park' and year=2016

[ali_hijazi]

you can use the wildmatch function

example:

where wildMatch(%KEY_BUDGET,'*2016*')>0

[pascos88]

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

[ali_hijazi]

[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

[sunny_talwar]

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

[pascos88]

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

[sunny_talwar]

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

];