Applymap for Dates problem

[#1]

Hi,

I am trying o use applymap for date values and am having trouble getting the correct values. I am generating a date table with correspoinding date numbers ( as in 0 for the first date to n for the last date). This is my mapping table. In my other table I am trying to pull the dateno from the mapping table based on another date field. My script is as follows:

Let vMinDate = num(Date#('01/01/2009','DD/MM/YYYY'));
Let vMaxDate = num(Date#('31/12/2009','DD/MM/YYYY'));
mHolidays:
mapping LOAD Date(Date,'DD/MM/YYYY') as Date,1
FROM
Holidaylist.xls
(biff, embedded labels, table is holidays$);
Datestmp:
Load Date($(vMinDate)+rowno()-1) as Date
autogenerate($(vMaxDate)-$(vMinDate)+1);
Dates:
mapping Load Date,
 rangesum(peek('DateNo'),if(match(num(Weekday(Date)),6)=0,if(applymap('mHolidays',Date)=1,0,1),0)) as DateNo
 resident Datestmp;
drop table Datestmp;
tbluser:
LOAD sdat,
 applymap('Dates',sdat) as sdatno,
 user
FROM
test.xls
(biff, embedded labels, table is Sheet1$);

The problem is that the value I am getting is 1 for all the fields instead of their dateno. Is this something to do with date formats? My date format is DD/MM/YYYY and if I write an inline table with date and dateno (not autogenerated), it seems to work. Can someone help me out here. I am attaching a sample file.

Nimish

[#2]

Hi,

Can someone please help with this? I have also tried using map.. using. Bu the same results.

Nimish

[prieper]

Can you explain, what the purpose is in the calculation?
So far you have managed to replace certain dates with a "1", which is most likely not wanted.

Peter

[#4]

Hi Peter,

Thanks for responding. The real scenario is that I have a database of jobs done with various dates for each job eg. job uploaded on, data preperation completed on, coding completed on, reveiw completed on, job submitted on. Need to find out the turn aournd time at each stage. Each job is a single record with all these dates. Also the turn aournd is based on the number of actual working days. One way was to have a formula which counts the working days. I have that working. But by this method, I am creating datenumbers as suggested by one of the members (Morgan) - http://community.qlik.com/forums/p/24482/93503.aspx#93503

The advantage is that the calculatin for Turn around time will become simple in terms of subtracting the datenumbers.

This is the reason, I am trying to map the date numbers from a mapping table of dates and date numbers.

I hope I have been able to explain.

I want to know whether I am using the applymap correctly. If yes, why would it not work.

Nimish

[prieper]

Have not worked that much with apply-map, but think that expression are evaluated only after the running of the script. Have you tried other solution? My first approach would be to load the holidays and then to evaluate the dates with EXISTS-function, like IF(EXISTS(MyDate), 0, 1).*

* actually you may use just the EXISTS(MyDate) and end with either 0 or -1 as result.

Peter

[#6]

Hi Peter,

The autogenerate and getting the dateno part of the script runs perfectly. I have tested this. The problem is arising only when mapping the dates. If I use an inline load of the dates and then work with the applymap, it works. Attached is the sample. So I am not sure where is the difference between this inline table and the autogenerated table.

Nimish

[#7]

Can anyone help me out in understanding what is going wrong in what I am doing.

Nimish