Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Creating Inline table using Months

I am creating an Inline table Using a field that already exists, Month. This is so I can order them according to my Fiscal Calendar. My Inline table looks like :

LOAD * INLINE [

    ap, Month, Quarter

   

    1, Jul, Q1

    2, Aug, Q1

    3, Sep, Q1

    4, Oct, Q2

    5, Nov, Q2

    6, Dec, Q2

    7, Jan, Q3

    8, Feb, Q3

    9, Mar, Q3

    10, Apr, Q4

    11, May, Q4

    12, Jun, Q4

];

But when I load it, it does not work exactly right..  on another tab, I have :

Month("ORDER_DATE") as Month

so in the list box, I have doubled names of months, with the ones from the inline table not doing anything with my data as if there is no link between the two. In the table viewer, it says my inline table is linked to my main table via Month

Based on what I have said, can anyone point out what I may be doing incorrectly?

1 Solution

Accepted Solutions
simenkg
Specialist
Specialist

Hmm. Ok.


Let's try something else.

In the table (Table1) where you have Month("ORDER_DATE") as Month

Create another field:
Num(Month("ORDER_DATE")) as MonthNum


Then add this at the end.


left join(Table1)

LOAD * INLINE [

    MonthSort, MonthNum

 

    1, 7

    2, 8

    3, 9

    4, 10

    5, 11

    6, 12

    7, 1

    8, 2

    9, 3

    10, 4

    11, 5

    12, 6

];

Now use sort by expression and have the expression = [MonthSort]

If you dont want the extra field MonthNum to stay in your data model add the folloing line

drop field MonthNum;

View solution in original post

6 Replies
simenkg
Specialist
Specialist

Months are Duals in QlikView. so really Dual('Jul',7) as Month is the way to define it.

There is a very neat way to make QlikView sort it the way you want;

At the start of your script create an inline table:


InitMonths:

Load * inline [
Month

7

8

9

10

11

12

1

2

3

4

5

6

];

Then at the end of your script write

drop table InitMonths;


Now when you want to sort your months you select Load Order - Original.

Anonymous
Not applicable
Author

That was very helpful, using this method I was able to link my quarters and get rid of the extra month names in the list box, but I am not able to order the month list box in order starting with July as month 1.

I went under the sort tab of the properties box, but none of the settings achieve this. When I sort by Original Load Order it is a very odd order, Going, Jun, May, Sep, Feb, Oct, Jan, Apr, Nov, Dec, Mar, Jul, Aug

Thanks!

simenkg
Specialist
Specialist

Does this not do what you want?

LoadOrder.PNG

Anonymous
Not applicable
Author

It is but the order (according to the way they are listed in the inline table) should be

Jul, Aug, Sep, Oct, Nov, Dec, Jan, Feb, Mar, Apr, May, Jun

but instead it is

Jun, May, Sep, Feb, Oct, Jan, Apr, Nov, Dec, Mar, Jul, Aug.. which is not in any order that I can see.


simenkg
Specialist
Specialist

Hmm. Ok.


Let's try something else.

In the table (Table1) where you have Month("ORDER_DATE") as Month

Create another field:
Num(Month("ORDER_DATE")) as MonthNum


Then add this at the end.


left join(Table1)

LOAD * INLINE [

    MonthSort, MonthNum

 

    1, 7

    2, 8

    3, 9

    4, 10

    5, 11

    6, 12

    7, 1

    8, 2

    9, 3

    10, 4

    11, 5

    12, 6

];

Now use sort by expression and have the expression = [MonthSort]

If you dont want the extra field MonthNum to stay in your data model add the folloing line

drop field MonthNum;

Anonymous
Not applicable
Author

Alright it didn't work, but I was able to use what you did there and then add monthname as a column and use that in the listbox, and that works.

Thanks for the help!