How to combine date fields

[#1]

Hello,

I need to combine some date fields into one date.  Currently the data looks like this.

Month in MM format

Year in YYYY format

I would like to combine them together into a new field and have it displayed in MM/YYYY format.

Any help would be much appreciated.

Thanks - Jason

[r_wroblewski · Accepted solution]

Hi Jason

you can simply use

[Month] & '/' & [Year]   as   [YearMonth],

in your script. Problem is that this new field is a string.

Better solution would be

Date(MakeDate([Year],[Month]),'MM/YYYY')     as     [YearMonth],

in your script.

Let me know if it works for you.

Best regards,

Ronny

[#3]

So that worked perfect.  My issue now is that the Months are coming in as names and I converted them into numbers in the table but I cannot seem to use the new field (InfoMonthNumber) unless I do a resident load on a new tab or below the load.  Is there anyway around this?  I would like the YearMonth to be in the same table.

InfoSec:

LOAD Site as OU,

     [Lumension Installs],

     [Systems in AD],

     [Install Percent],

     [Microsoft Patch %],

     [3rd Party Patch %],

     Month as InfoSec_Month,

     Year as InfoSec_Year,

     num(month(date#(Month,'MMM'))) as InfoMonthNumber //Convert Month name into Month Number

FROM

[..\Patch.xlsx]

(ooxml, embedded labels, table is Sheet1);

DateConversion:

Load

  InfoSec_Year,

  InfoMonthNumber,

    Date(MakeDate([InfoSec_Year],[InfoMonthNumber]),'MM/YYYY') as [InfoSecMonthYear]

Resident InfoSec

;

[r_wroblewski]

Hi Jason

just use left join function.

Left Join(InfoSec)

Load *

Resident DateConversion;

Regards,

Ronny

[#5]

That worked, thanks for the help.