topic Re: Creating Inline table using Months in QlikView

Creating Inline table using Months

Anonymous — Fri, 18 Jul 2014 18:03:11 GMT

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?

Re: Creating Inline table using Months

simenkg — Fri, 18 Jul 2014 18:10:46 GMT

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

];

Then at the end of your script write

drop table InitMonths;

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

Re: Creating Inline table using Months

Anonymous — Fri, 18 Jul 2014 18:22:03 GMT

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!

Re: Creating Inline table using Months

simenkg — Fri, 18 Jul 2014 18:26:36 GMT

Does this not do what you want?

Re: Creating Inline table using Months

Anonymous — Fri, 18 Jul 2014 18:31:13 GMT

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.

Re: Creating Inline table using Months

simenkg — Fri, 18 Jul 2014 18:43:10 GMT

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;

Re: Creating Inline table using Months

Anonymous — Fri, 18 Jul 2014 19:07:37 GMT

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!