topic Re: How to Create YYYYMM Field from Load in QlikView

How to Create YYYYMM Field from Load

Thu, 25 Jun 2015 13:54:22 GMT

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.

Re: How to Create YYYYMM Field from Load

alexandros17 — Thu, 25 Jun 2015 13:59:49 GMT

You create a YYYMM field starting from a date field, so let's suppose your date field is SalesDate then

Load

...

SalesDate,

year(SalesDate)*100 + Month(SalesDate) as YearMonthField,

...

Re: How to Create YYYYMM Field from Load

rubenmarin — Thu, 25 Jun 2015 14:01:44 GMT

Hi Thabiso, as an example, if you have a field named DateField, you can do:

LOAD Date(DateField, 'YYYYMM') as YearMonth

...

Re: How to Create YYYYMM Field from Load

Thu, 25 Jun 2015 14:03:26 GMT

Hi Alessandro,

Im trying to Combine two Fields Year and Month Fields to make them as one Field YearMonth

Thanks

Re: How to Create YYYYMM Field from Load

vishsaggi — Thu, 25 Jun 2015 14:05:09 GMT

Hi Thabiso,

Try this might work.

DateEg:

LOAD ID, Date(Date, 'YYYYMM') As Date;

LOAD * INLINE [

ID, Date

1, 2015-01-01

2, 2015-02-22

3, 2015-08-20

4, 2015-04-22

];

Re: How to Create YYYYMM Field from Load

rwunderlich — Thu, 25 Jun 2015 14:05:19 GMT

Date(MakeDate(Year, Month), 'YYYYMM') as YearMonth

-Rob

Re: How to Create YYYYMM Field from Load

alexandros17 — Thu, 25 Jun 2015 14:07:22 GMT

So:

Load

...

Year*100 + Month as YearMonthField,

...

Re: How to Create YYYYMM Field from Load

buzzy996 — Thu, 25 Jun 2015 14:09:50 GMT

try with rwunderlich suggestion,tht will works 100%.

Re: How to Create YYYYMM Field from Load

vijetas42 — Thu, 25 Jun 2015 14:13:15 GMT

Hi,

You can use QlikView Date functions like,Date('Date_Field',Format of Date) where you can specify 'YYYYMM'

Re: How to Create YYYYMM Field from Load

Thu, 25 Jun 2015 14:13:36 GMT

Hi Shiva,

It dosent work that solution, it makes the Field Blank.

Re: How to Create YYYYMM Field from Load

Thu, 25 Jun 2015 14:18:35 GMT

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.

Re: How to Create YYYYMM Field from Load

rubenmarin — Thu, 25 Jun 2015 14:20:03 GMT

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

Re: How to Create YYYYMM Field from Load

buzzy996 — Thu, 25 Jun 2015 14:20:26 GMT

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

Date(Date,'YYYYMM') as Date,

Re: How to Create YYYYMM Field from Load

rubenmarin — Thu, 25 Jun 2015 14:24:47 GMT

You can try with this:

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

Re: How to Create YYYYMM Field from Load

alexandros17 — Thu, 25 Jun 2015 14:45:09 GMT

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

Re: How to Create YYYYMM Field from Load

rwunderlich — Fri, 26 Jun 2015 02:57:05 GMT

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

Re: How to Create YYYYMM Field from Load

Fri, 26 Jun 2015 09:27:08 GMT

Hi,

can anyone explain this to me in simple terms

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

Re: How to Create YYYYMM Field from Load

rubenmarin — Fri, 26 Jun 2015 09:34:30 GMT

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.

Re: How to Create YYYYMM Field from Load

Fri, 26 Jun 2015 09:40:08 GMT

Hi Ruben,

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

Thanks

Re: How to Create YYYYMM Field from Load

rubenmarin — Fri, 26 Jun 2015 10:03:42 GMT

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