topic Re: Sorting MonthNames in proper order in QlikView

Sorting MonthNames in proper order

Fri, 20 Nov 2020 18:02:30 GMT

Hello all,

I have used load inline to convert Month in Month names, but the month names are shown in random order. How can it be achieved using MonthNames(variable used in qlikview by default in SET statement)or Month(date) function in edit script.

Thanks

Re: Sorting MonthNames in proper order

Clever_Anjos — Wed, 21 Jan 2015 14:00:25 GMT

Would you mind posting your script?

Re: Sorting MonthNames in proper order

alexandros17 — Wed, 21 Jan 2015 14:04:01 GMT

In the object you are using there is a Tab "Sort", uncheck everithing and check Load Order

Let me know

Re: Sorting MonthNames in proper order

Wed, 21 Jan 2015 14:04:28 GMT

LOAD Year,

[Order Type],

Distributor,

[SKU ID],

[Actual Volume],

[Actual Value]

FROM

[DS2 - Sales.xlsx]

(ooxml, embedded labels, table is Sheet1);

LOAD

* INLINE

[ Month, Month_Name

1, Jan

2, Feb

3, Mar

4, Apr

5, May

6, Jun

7, Jul

8, Aug

9, Sep

10, Oct

11, Nov

12, Dec ];

How can i sort the month names in arranged order using ONLY edit script and not sort it in the expression,

Re: Sorting MonthNames in proper order

Clever_Anjos — Wed, 21 Jan 2015 14:15:52 GMT

try this:

LOAD

Month, dual(Month,Month_Name) INLINE

[ Month, Month_Name

1, Jan

2, Feb

3, Mar

4, Apr

5, May

6, Jun

7, Jul

8, Aug

9, Sep

10, Oct

11, Nov

12, Dec ];

Re: Sorting MonthNames in proper order

MarcoWedel — Wed, 21 Jan 2015 16:03:18 GMT

When using the proper date formatting and interpretation functions, there will be no need for special translation tables.

For example, if you like to convert an integer representing a month to a month name, you could try with:

LOAD Month(Date#(MonthAsNumberField,'M')) as MonthName,
          someotherfields
FROM YourSource;

this way you get a dual value, meaning a textual representation of an underlying numerical value, i.e. sorting does not require any additional efforts.

Re: Sorting MonthNames in proper order

Anonymous — Wed, 21 Jan 2015 16:29:39 GMT

Dual is a best way, I think. Just a small correction - first parameter is string, the second is a number:

dual(Month_Name,Month) as Month

Re: Sorting MonthNames in proper order

Clever_Anjos — Wed, 21 Jan 2015 16:33:37 GMT

Thanks Michael for fixing my code