topic Re: Applymap with dates in QlikView

Applymap with dates

bobbydave — Wed, 25 Nov 2020 16:16:04 GMT

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?

Re: Applymap with dates

mdmukramali — Thu, 14 Sep 2017 11:37:13 GMT

Hi David,

Can you share Sample Data App.

Re: Applymap with dates

sunny_talwar — Thu, 14 Sep 2017 11:37:28 GMT

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

Re: Applymap with dates

Peter_Cammaert — Thu, 14 Sep 2017 12:00:42 GMT

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.

Re: Applymap with dates

its_anandrjs — Thu, 14 Sep 2017 12:27:20 GMT

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

You can try

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

Re: Applymap with dates

Anonymous — Thu, 14 Sep 2017 12:37:42 GMT

Hi,

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

Re: Applymap with dates

bobbydave — Thu, 14 Sep 2017 12:52:03 GMT

!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.

Re: Applymap with dates

its_anandrjs — Thu, 14 Sep 2017 12:57:34 GMT

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

Re: Applymap with dates

its_anandrjs — Thu, 14 Sep 2017 13:00:18 GMT

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);