Month format

[#1]

Hi

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.

[MK_QSL]

Left(MonthFieldTable2,3) as MonthFieldTable1

Update :

Load

  Month as MonthNum,

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

Inline

[

  Month

  1

  2

  3

  4

  5

  6

  7

  8

  9

  10

  11

  12

];

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

];

[ariel_klien]

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

BR

Ariel

[#4]

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.

[MK_QSL]

Use

Left(Months,3) as MonthNames,

For PRODUCT table

Month as MonthNum,

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

for XZY Table

[ariel_klien]

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

[MK_QSL · Accepted solution]

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;

[MarcoWedel]

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

[#9]

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

[MarcoWedel]