Applymap with dates

[bobbydave]

Hi guys,

I am using ApplyMap which is causing issues Dates.

ApplyMap('MapDateToFiscalWeek',  [Payment Run Date]) as [Fiscal Year Week]

where [payment run date] is in DD/MM/YYYY

MapDateToFiscalWeek:

Mapping

LOAD Date,

     Year(Date#(Left(Fiscal_Year,4),'YYYY')) & Fiscal_Week as YearWeek

FROM

[$(vQVDs)\T_Calendar_Test.qvd](qvd);

It only shows me the last 10 weeks which is an issue also.

I'm imagining that this should show me the [fiscal year-fiscal week] when it works out.

Of course, this does not want to work for me.

Any ideas?

[mdmukramali]

Hi David,

Can you share Sample Data App.

[sunny_talwar]

Would you be able to share a sample to show what you have and explain what you need from it?

[Peter_Cammaert]

Are you saying that the translation works for the last 10 weeks? In that case, does your QVD contain more source data for the mapping table? Also check whether the content of field [Payment Run Date] contains the exact same data type as the first field in your QVD (strings & Strings, Duals & Duals, Numbers & Numbers)

[Edit] Made my question a bit clearer.

[its_anandrjs]

Without seen and sample or your data it is tough to say but in the below script you find fiscal year left of 4 what it gives to you a year YYYY.

Year(Date#(Left(Fiscal_Year,4),'YYYY')) & Fiscal_Week as YearWeek

Or

You can try

Year(Date#(Left(Fiscal_Year,4),'YYYY')) & Week( DateField ) as  Fiscal_Week

[Anonymous]

Hi,

Perhaps I do not understand your need but why would you want to use Applymap for dates instead of IntervalMatch?

[bobbydave]

!App attached.

My dates dont seem to be registering with Fiscal Year Week

On the Weekly Validation page, I am trying to map the dates.

[its_anandrjs]

In any other QVW file load your Mapping table by removing Mapping before load and check did you get correct out put.

[its_anandrjs]

Also check this by using Makedate function in mapping load

MapDateToFiscalPeriod:

Mapping

LOAD Date,

    Date(MakeDate(Fiscal_Year,Fiscal_Period,Day(Formatted_Date)),'YYYYMM') as YearMonth

FROM

[$(vQVDs)\T_Calendar_Test.qvd](qvd);