Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

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

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

1 Solution

Accepted Solutions
MVP
MVP

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

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.

2 Replies
Not applicable

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

2011-10-12 23h23_29.png

MVP
MVP

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

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.

Community Browser