load date to get day,month,year

[#1]

Hi there,

How can I get the Day, Month, Year from the [WO Created], which is in date/time format.

Thanks

LOAD [WO No],

     [Cntr ID],

     [Cntr No],

     [WO Created],

     Terminal,

, 

FROM

(ooxml, embedded labels, table is WO_with_Job_codes_table);

Thanks

[Anil_Babu_Samineni]

Use this?

LOAD [WO No],

    [Cntr ID],

    [Cntr No],

    [WO Created],

    Terminal,

    [Code],

    Year([WO Created]) as Year,

    Day([WO Created]) as Day,

    Month([WO Created]) as Month

FROM

(ooxml, embedded labels, table is WO_with_Job_codes_table);

[#3]

Hi

How can I load only certain month data for e.g Jan , Feb , Mar under year 2017 only.

Thanks

[Anil_Babu_Samineni]

May be this?

LOAD [WO No],

    [Cntr ID],

    [Cntr No],

    [WO Created],

    Terminal,

    [Code],

    Year([WO Created]) as Year,

    Day([WO Created]) as Day,

    Month([WO Created]) as Month

FROM

(ooxml, embedded labels, table is WO_with_Job_codes_table) where Match(Year,'2017') and Match(Month, 'Jan','Feb','Mar');

[#5]

go this error:

Field not found - <Year>

LOAD [WO No],

     [Cntr ID],

     [Cntr No],

     [WO Created],

      [Job description],

    Year([WO Created]) as Year,

    Day([WO Created]) as Day,

    Month([WO Created]) as Month

FROM

(ooxml, embedded labels, table is WO_with_Job_codes_table)where Match(Year,'2017') and Match(Month, 'Jan','Feb','Mar')

[Anil_Babu_Samineni]

May be try simple way with Preceding load

LOAD * Where Year = 2017 and Month = WildMatch(Month,'Jan','Feb','Mar');

LOAD [WO No],

    [Cntr ID],

    [Cntr No],

    [WO Created],

    Terminal,

    [Code],

    Year([WO Created]) as Year,

    Day([WO Created]) as Day,

    Month([WO Created]) as Month

FROM

(ooxml, embedded labels, table is WO_with_Job_codes_table);

[Kushal_Chawda]

Data:

LOAD *

Where Year ='2017';

LOAD [WO No],

     [Cntr ID],

     [Cntr No],

     [WO Created],

      [Job description],

   Year(floor([WO Created])) as Year,

    Day(floor([WO Created])) as Day,

    Month(floor([WO Created])) as Month

FROM

(ooxml, embedded labels, table is WO_with_Job_codes_table);

[Anil_Babu_Samineni]

Or this

Sample:

LOAD [WO No],

    [Cntr ID],

    [Cntr No],

    [WO Created],

      [Job description],

  Year(floor([WO Created])) as Year,

    Day(floor([WO Created])) as Day,

    Month(floor([WO Created])) as Month

FROM

(ooxml, embedded labels, table is WO_with_Job_codes_table);

NoConcatenate

LOAD * Resident Sample Where Year = 2017 and Month = WildMatch(Month,'Jan','Feb','Mar');

DROP Table Sample;

[vinieme12]

kokleong_ong  No need to do preceding load

Data:

LOAD [WO No],

     [Cntr ID],

     [Cntr No],

     [WO Created],

      [Job description],

   Year(floor([WO Created])) as Year,

    Day(floor([WO Created])) as Day,

    Month(floor([WO Created])) as Month

FROM

(ooxml, embedded labels, table is WO_with_Job_codes_table)

Where Year(floor([WO Created])) = 2017 and Month(floor([WO Created])) <=3 ;

[#10]

Hi there

I trying to add on the last script to exclude the [Job code] values as listed but seem not working.

LOAD [WO No],

     [Cntr ID],

     [Cntr No],

     [WO Created],

     [Job code],

     [Job description],

    Year([WO Created]) as Year,

    Day([WO Created]) as Day,

    Month([WO Created]) as Month

FROM

(ooxml, embedded labels, table is WO_with_Job_codes_table);

NoConcatenate

LOAD * Resident WO_with_Job_codes_table Where Year = 2017 and Month = WildMatch(Month,'Jan','Feb','Mar');

LOAD * Resident WO_with_Job_codes_table Where [Job code] <> WildMatch([Job code],'1104','1106','1109','1136','1750','1801','1802','1803','1824','1825','2001','2002','2003','2004','2006','2007','2010','2012','2013','2014','2015','2017','2018','2019','2020');

DROP Table WO_with_Job_codes_table;