Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

use of a pick() in a load script to substitute month name

I have source data, which does not have a date, only a month number and year.

I created an inline table:

Month_Sort:

Load * Inline [

RA_Month, MthSortOrder

Jan,1

Feb,2

Mar,3

Apr,4

May,5

Jun,6

Jul,7

Aug,8

Sep,9

Oct,10

Nov,11

Dec,12

];

.... and then in my transactions load, did this:

pick(MonthNumber,

'Jan',

'Feb',

'Mar',

'Apr',

'May',

'Jun',

'Jul',

'Aug',

'Sep',

'Oct',

'Nov',

'Dec'

) as RA_Month

It works, but is there a better way?

1 Solution

Accepted Solutions
swuehl
Not applicable

Re: use of a pick() in a load script to substitute month name

Month() function creates a dual with a numeric representation you can use for sorting. No need for the extra table (which probably causes the problem, because you are linking a dual with a text field).

Try removing your Month_Sort table.

7 Replies
swuehl
Not applicable

Re: use of a pick() in a load script to substitute month name

Try

Month( makedate( YearField, MonthNumber)) as Month

Not applicable

Re: use of a pick() in a load script to substitute month name

Much cleaner - thanks!

Not applicable

Re: use of a pick() in a load script to substitute month name

So, I tried this:

Month( makedate( YearField, MonthNumber)) as Month

For some reason it duplicates Oct Nov and Dec in the list of months.

I then reloaded from the pick() code, and it did not duplicate those months. 

Your code looks fine - looked it up in the date functions.

???

swuehl
Not applicable

Re: use of a pick() in a load script to substitute month name

Strange, I believe it shouldn't duplicate any value.

Are you link the Month field to another table? If so, how do you create the month over there?

Is it possible that you post a small sample file?

Not applicable

Re: use of a pick() in a load script to substitute month name

I do link that Month field:

Month_Sort:

Load * Inline [

RA_Month, MthSortOrder

Jan,1

Feb,2

Mar,3

Apr,4

May,5

Jun,6

Jul,7

Aug,8

Sep,9

Oct,10

Nov,11

Dec,12

];

So, the Month( makedate( @102, @104)) as RA_Month     ...should associate properly.

Whe I get a chance, I will create a small sample.

swuehl
Not applicable

Re: use of a pick() in a load script to substitute month name

Month() function creates a dual with a numeric representation you can use for sorting. No need for the extra table (which probably causes the problem, because you are linking a dual with a text field).

Try removing your Month_Sort table.

Not applicable

Re: use of a pick() in a load script to substitute month name

Awesome - will try that.

So, it creates another column that can be selected in the sort?