Converting month column with working day into Date and working day columns

[#1]

Hello everybody,

I currently have a table with this structure :

A Year colmun (JARH), a MON01 column, MON02, .... MON12 colunm

Each MON* column contains the working days of the month

My goal is to get a table with this scruture :  Date / Open ?

For exemple :

Date - Open

10/10/2011   -  0

11/10/2011 - 1

Any ideas ?

Regards

Kevin

[#2]

[swuehl · Accepted solution]

Hi,

I would first use a CROSSTABLE LOAD to get the Month header as field Value in e.g. a new Month field.

Please check the Crosstable load in the Help (and then you could use the assistent in the editor).

This should give you a table with one row  per year and month, and still a connected Field with values 00101010100111... or similar (binary flags for Open/Close).

Then use a should transform Year and  Monthname to a date MonthStartField  using monthstart(Makedate(Year, right(Monthfield,2)))

One more load will transform the FlagFields into one record per Day

Load

...

Mid(FlagField,iterno(),1) as OpenFlag,

Makedate(year,Month(MonthstartField),iterno()) as Date

...

resident ... while iterno() <= day(monthend(MonthStartField))

Maybe something like this, I think.

Hope this helps,

Stefan

edit: Please look at attached sample.