Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
Sorry there may be some inconvenience with the example table. Here's a picture:
dj_skbs
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;
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
I failed to see it's only one qualifier field:
crosstable(MONAT, Data,1)
Thank You.