Double As

[Chanty4u]

Hi all,

When i load below script it is givng error as   Filed not found  <Date>

how to over come dis?

Sample:

LOAD F1 as [s.no],

     F2 as [Sales Executive],

     F3 as [TopLine Achieved],

     F4 as [Gross Margin Achieved] ,

     F5 as [Top Line Target] ,

     F6 as GMTarget,

     F7 as Product,

     F8 as BranchHead,

     F9 as  Branch,

     F10 as Dept ,

     F11  as Date

//           Year(Date) as Year,

//     Month(Date) as Month,

//     Day(Date) as Day,

//     Week(Date) as Week,

//    'Q' & ceil(Num(Month(Date))/3) as Quater,

//     applyMap('FiscalQuarterMap', num(month(Date)),null()) AS FiscalQuarter

from[...];

[PrashantSangle]

Hi,

Yes, That could be possible error if those field not commented in his original post.

Regards,

[Chanty4u]

same kush.... its nt givng the values in the field

[PrashantSangle]

Hi,

try with date#()

like

month(date#(dateField,'DD-MMM-yy')) as Month

Regards

[Kushal_Chawda · Accepted solution]

Your dates are in string format use Date#

Sample:

LOAD  *,

           Year(date#(Date,'DD-MMM-YY')) as Year,

     Month(date#(Date,'DD-MMM-YY')) as Month,

    Day(date#(Date,'DD-MMM-YY')) as Day,

    Week(date#(Date,'DD-MMM-YY')) as Week,

    'Q' & ceil(Num(Month(date#(Date,'DD-MMM-YY')))/3) as Quater,

     applyMap('FiscalQuarterMap', num(month(Date)),null()) AS FiscalQuarter;

LOAD F1 as [s.no],

     F2 as [Sales Executive],

     F3 as [TopLine Achieved],

     F4 as [Gross Margin Achieved] ,

     F5 as [Top Line Target] ,

     F6 as GMTarget,

     F7 as Product,

     F8 as BranchHead,

     F9 as  Branch,

     F10 as Dept ,

     F11  as Date

from[...];

[Anonymous]

Hi Chanty,

This is because your format on your original table is not on Date format. Could you share one example of your data?

Thanks

[Anonymous]

Hi Chanty,

Try kushal chawda solution. On Applymap apply the Date# expression too.

Regards!

[Chanty4u]

yes its givng correct now...but Quarter value shwng as only   "Q"

instead of Q1 and Q2 Q3 Q4...?

[Chanty4u]

evrythng is wrkng except  fiscal quarter

[PrashantSangle]

Hi,

try

'Q' & ceil(Month(date#(Date,'DD-MMM-YY'))/3) as Quater

Regards

[tamilarasu]

Should be this,

applyMap('FiscalQuarterMap', num(month(Date#(Date,'DD-MMM-YYYY'))),null()) AS FiscalQuarter;