Link Holiday calendar (SAP) to master calendar

[#1]

Hello Qver,

I want to link a holiday calendar (SAP) to a master calender:

The holiday calendar looks like:

Year    JAN                                                     FEB                                                 MAR

2009    0111111111111111111111111111111    1111111111111111111111111111       1111111111111111111111111111111

2010    0111111111111111111111111111111    1111111111111111111111111111       1111111111111111111111111111111

2011    0111111111111111111111111111111    1111111111111111111111111111       1111111111111111111111111111111

2012    0111111111111111111111111111111    11111111111111111111111111111      1111111111111111111111111111111

2013    0111111111111111111111111111111    1111111111111111111111111111       1111111111111111111111111111010

2014    0111111111111111111111111111111    1111111111111111111111111111       1111111111111111111111111111111

2015    0111111111111111111111111111111    1111111111111111111111111111       1111111111111111111111111111111

That means each digit (0=Holiday or 1=No Holiday) is representing one day of the month. I.e. JAN=0 means

January 1st; last digit in FEB 2012 means 29.02. etc.

No delimiter exists between the digits.

Til now my experiencees to solve that ideally were not successful. So I will ask You for any idea please.

Thank You. dj_skbs

[#2]

Sorry there may be some inconvenience with the example table. Here's a picture:

dj_skbs

[ekech_infomotio · Accepted solution]

I think you are talking about SAP table TFACS 😉

Here is what I do with this table:

(Mind that I am pulling this table directly via SAP-Connector from SAP, so my first load is from a SQL SELECT

some translation as my examples are in German

MONAT = Month

JAHR = Year

vJahrVon = vYearFrom

vJahrBis = vYearTo

laenge = length

istArbeitstag = isWorkingday

)

Regards,

Edgar

tmp1TFACS:

CrossTable(MONAT, Data, 2)

LOAD IDENT,

     JAHR,

     MON01,

     MON02,

     MON03,

     MON04,

     MON05,

     MON06,

     MON07,

     MON08,

     MON09,

     MON10,

     MON11,

     MON12

where

          JAHR >= $(vJahrVon)

          and

          JAHR <= $(vJahrBis)

;

sql select * from TFACS

;

tmp2TFACS:

CrossTable(TAG, Data, 3)

load

          IDENT,

          JAHR,

          right(MONAT, 2)          as MONAT,

//          len(Data)          as laenge,

          mid(Data, 1,1)          as 01,

          mid(Data, 2,1)          as 02,

          mid(Data, 3,1)          as 03,

          mid(Data, 4,1)          as 04,

          mid(Data, 5,1)          as 05,

          mid(Data, 6,1)          as 06,

          mid(Data, 7,1)          as 07,

          mid(Data, 8,1)          as 08,

          mid(Data, 9,1)          as 09,

          mid(Data, 10,1)          as 10,

          mid(Data, 11,1)          as 11,

          mid(Data, 12,1)          as 12,

          mid(Data, 13,1)          as 13,

          mid(Data, 14,1)          as 14,

          mid(Data, 15,1)          as 15,

          mid(Data, 16,1)          as 16,

          mid(Data, 17,1)          as 17,

          mid(Data, 18,1)          as 18,

          mid(Data, 19,1)          as 19,

          mid(Data, 20,1)          as 20,

          mid(Data, 21,1)          as 21,

          mid(Data, 22,1)          as 22,

          mid(Data, 23,1)          as 23,

          mid(Data, 24,1)          as 24,

          mid(Data, 25,1)          as 25,

          mid(Data, 26,1)          as 26,

          mid(Data, 27,1)          as 27,

          mid(Data, 28,1)          as 28,

          mid(Data, 29,1)          as 29,

          mid(Data, 30,1)          as 30,

          mid(Data, 31,1)          as 31

resident

          tmp1TFACS

;

kalender:

load

          IDENT,

          JAHR,

          MONAT,

          TAG,

          date#(JAHR & '-' & MONAT & '-' & TAG, 'YYYY-MM-DD')          as DATUM,

          date#(JAHR & '-' & MONAT & '-' & TAG, 'YYYY-MM-DD')          as Datum,

          JAHR & '/' & MONAT                                                                                          as JahrMonat,

          Data                                                                                                                        as istArbeitstag

resident

          tmp2TFACS

where

          not isnull(Data)

          and

          match(Data, '') = 0

;

drop table tmp1TFACS;

drop table tmp2TFACS;

[#4]

Thank You Edgar.

BEcause I have no possibility to connect SAP directly I'm using the table TFACS as one gets it with transaction SE16.

I transformed Your script in that way and it works but always lacking the first Month (=JANUARY) of a year.

Can You see why? See attached QVW.

Greetings

[#5]

I failed to see it's only one qualifier field:

crosstable(MONAT, Data,1)

Thank You.