topic Month format in QlikView

Month format

Fri, 20 Nov 2020 18:02:30 GMT

I have two tables with Month column, in table 1, it shows month as 1, 2, 3, 4, ... 12 which i renamed using load* inline as jan feb...

but in table 2 , i have month column which has values like Jan, February, Mar, Apr, May, June... . how can i change the month format for this table2? so that month column has similar values which can later be easily joined.

Re: Month format

MK_QSL — Mon, 08 Sep 2014 12:47:40 GMT

Left(MonthFieldTable2,3) as MonthFieldTable1

Update :

Load

Month as MonthNum,

SubField('$(MonthNames)',';',Month) as MonthNames

Inline

[

Month

];

Load

Left(Months,3) as MonthNames,

Sales

Inline

[

Months, Sales

Jan, 100

February, 120

Mar, 140

Apr, 160

May, 120

June, 100

July, 80

Aug, 70

Septe, 160

Octob, 150

November, 90

Decem, 95

];

Re: Month format

ariel_klien — Mon, 08 Sep 2014 13:00:11 GMT

Hi,

You can use mapping.

something like that:

MonthMap:

mapping LOAD * INLINE [

Months, Month_num

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 in the table you use:

ApplyMap('MonthMap',Month_Field) as Month_Field

Ariel

Re: Month format

Mon, 08 Sep 2014 13:13:48 GMT

i have two tables very similar to

with respect ro month values in table XYZ, i changed it to

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

];

but load * inline i cant create associating it with month in PRODUCT table , it shows conflict.

Re: Month format

MK_QSL — Mon, 08 Sep 2014 13:22:11 GMT

Use

Left(Months,3) as MonthNames,

For PRODUCT table

Month as MonthNum,

SubField('$(MonthNames)',';',Month) as MonthNames

for XZY Table

Re: Month format

ariel_klien — Mon, 08 Sep 2014 13:22:42 GMT

So, use the mapping i wrote in my previous answer,

you need to update the names of the months in the inline.

you need to change the script to this:

MonthMap:

mapping LOAD * INLINE [

Months, Month_num

Jan, 1

Feb, 2

Mar, 3

Apr, 4

May, 5

Jun, 6

Jul, 7

Aug, 8

Sep, 9

Oct, 10

Nov, 11

Dec, 12

];

XYZ:

load year,

ApplyMap('MonthMap',Month) as Month,

Day,

...

from ....;

product:

load * from ....;

Ariel

Re: Month format

MK_QSL — Mon, 08 Sep 2014 13:32:44 GMT

PRODUCT:

Load

[Item Code],

[BRAND CODE],

Brand,

Left(Month,3) as Month,

Year

From TableName;

XYZ:

Load

Year,

SubField('$(MonthNames)',';',Month) as Month,

[Order Type],

Day....

From TableName;

Re: Month format

MarcoWedel — Mon, 08 Sep 2014 22:01:00 GMT

Hi,

always load dates as dates instead of strings circumventing any obstacles regarding sorting with special numerical sorting fields:

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December';

table1:
LOAD Month(Date#(Month, 'M')) as Month,
    Ceil(Rand()*100) as Field1
Inline [
    Month
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
];

table2:
LOAD Month(Date#(Month, 'MMM')) as Month,
    Ceil(Rand()*100) as Field2
INLINE [
    Month
    Jan
    February
    Mar
    Apr
    May
    June
    Jul
    August
    Sep
    Oct
    November
    Dec
];

this way you can format your month field as you wish and still can use it in date calculations.

hope this helps

regards

Marco

Re: Month format

Tue, 09 Sep 2014 05:44:17 GMT

it shows month listbox with values jan, feb, mar and also 1, 2... 12

Re: Month format

MarcoWedel — Tue, 09 Sep 2014 05:47:21 GMT

Re: Month format

Tue, 09 Sep 2014 07:20:01 GMT

Hi Ariel,

i did try it, but it shows me numeric and string values together

Re: Month format

Tue, 09 Sep 2014 07:33:31 GMT

Hi,

It works fine, but month values are shown in random order. i gave definition : [MonthName(date)as Month]

but issue still persists.

Re: Month format

aveeeeeee7en — Tue, 09 Sep 2014 07:36:41 GMT

If you have Month & Year than Use this Expression:

dual(left(monthname(makedate(YEAR,MONTH)),3),MONTH) AS MONTH_NEW

Example:

Load *, dual(left(monthname(makedate(YEAR,MONTH)),3),MONTH) AS MONTH_NEW Inline [

MONTH, YEAR

1, 2014

2, 2014

3, 2014

4, 2014

5, 2014

6, 2014

7, 2014

8, 2014

9, 2014

10, 2014

11, 2014

12, 2014

];

Also, see the Attachment.

Regards

Aviral Nag