How to Create YYYYMM Field from Load

[#1]

Hi All,

How do i create a new Field that contains Year and Month

e.g

201501

201502

201503

and so on.

Please provide Examples

Thanks.

[#2]

Hi,

Below is example of the Table i have:

Codes	Year	Month
0323	2015	January
0657	2015	January
0775	2015	February
0857	2015	March

What i want is:

Codes	YearMonth
0323	201501
0657	201501
0775	201502
0857	201503

Thanks.

[rubenmarin]

Hi Thabiso, Rob's suggestion will work if you have those 2 fields: Year and Month (both fields has to be numbers)

[buzzy996]

try tis,tested it's working..........

Date(Date,'YYYYMM') as Date,

[rubenmarin]

You can try with this:

Date(Date#(Month & '/' & Year, 'MMMM/YYYY'), 'YYYYMM') as YearMonth

[alexandros17]

Try with

AAA:
load * Inline [
Codes,myYear,myMonth
0323, 2015, January
0657, 2015, January
0775, 2015, February
0857, 2015, March
];

BBB:
LOAD Codes, myYear*100 + Div(Index(Replace('$(MonthNames)', ';', ''), Lower(Left(myMonth,3))), 3)+1 as YearMonth Resident AAA;
DROP Table AAA;

Where $(MonthNames) is the string that defines month names at the beginning of the document

[rwunderlich]

I didn't realize your Months were in text format. You just need to add a bit of coercion to translate the text to numerical format:

LOAD

  Date(MakeDate(Year,  month(date#(Month, 'MMMM'))  )

  ,'YYYYMM') as YearMonth,

  Codes,

    Year,

    Month

FROM

[https://community.qlik.com/thread/170025]

(html, codepage is 1252, embedded labels, table is @1);

-Rob

http://masterssummit.com

http://robwunderlich.com

[#8]

Hi,

can anyone explain this to me in simple terms

year(Date)*100 + Month(Date) as YearMonthField

[rubenmarin]

Hi Thabiso, you only need to the operations:

For 01/03/2015:

Year*100 = 201500

Month = 3

201500 + 3 =201503 <-- This creates a number in format YYYYMM, I must say this is not a date, just a number and you can't use some date functions like addmonths(), monthstart()... but it's still useful as a number.

[#10]

Hi Ruben,

So Initially 00 Represents the Starting number from Selected field even thou i can use month it will still be relevant?

Thanks

[rubenmarin]

Hi, sorry but I don't understand the question...

year(Date)*100 + Month(Date) as YearMonthField //<--This is made using a Date Field while reloading, so there is no selections.

From that Date field it takes the year a multiplies by 100, leaving the last two digits to represent the month