topic Re: date in QlikView

date

userid128223 — Tue, 23 Oct 2012 16:03:36 GMT

I am reading data from the text file trying to create master calander for date that is being read as string.

Table Name:

LOAD

Name

Number

Model

Make

orderdate

Date( Date#( orderdate, 'YYYYMMDD'), 'YYYYMMDD') as new_orderdate,

from

.........

However when I try using new_orderdate in master calander it gives me field not found error.

Re: date

MayilVahanan — Tue, 23 Oct 2012 16:19:53 GMT

Try like this

Date( orderdate, 'YYYYMMDD') as new_orderdate

May be its already in correct format..

Re: date

swuehl — Tue, 23 Oct 2012 16:20:34 GMT

What do you mean with "using new_orderdate in master calander"? Can you post your relevant code snippet in which you create the master calendar? You can only reference the field new_orderdate when this is part of the input source, i.e. for example when resident loading your above table. It is not just known in global context across tables.

Stefan

Re: date

Tue, 23 Oct 2012 16:24:44 GMT

I noticed there is a comma at the end of the new_orderdate alias. Might this be the reason you are getting an error?

orderdate

Date( Date#( orderdate, 'YYYYMMDD'), 'YYYYMMDD') as new_orderdate,

from

Re: date

userid128223 — Tue, 23 Oct 2012 16:31:00 GMT

Thanks for all the answers, I think the issue is referencing as Swuehl points out. Here is it code in more detail.

[Sales Table]:

LOAD

Name,

Number,

Model,

Make,

orderdate,

Date( Date#( orderdate, 'YYYYMMDD'), 'YYYYMMDD') as new_orderdate

from

......... (text file)

// MASTER CALENDER

QuartersMap:

MAPPING LOAD

rowno() as Month,

'Q' & Ceil (rowno()/3) as Quarter

AUTOGENERATE (12);

Temp:

Load

min(new_orderdate) as minDate,

max(new_orderdate) as maxDate

Resident [Sales Table];

Let varMinDate = Num(Peek('minDate', 0, 'Temp'));

Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));

DROP Table Temp;

TempCalendar:

LOAD

$(varMinDate) + Iterno()-1 As Num,

Date($(varMinDate) + IterNo() - 1) as TempDate

AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

MasterCalendar:

Load

TempDate AS OrderDate,

week(TempDate) As Week,

Year(TempDate) As Year,

Month(TempDate) As Month,

Day(TempDate) As Day,

YeartoDate(TempDate)*-1 as CurYTDFlag,

YeartoDate(TempDate,-1)*-1 as LastYTDFlag,

inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12,

date(monthstart(TempDate), 'MMM-YYYY') as MonthYear,

ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,

Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,

WeekDay(TempDate) as WeekDay

Resident TempCalendar

Order By TempDate ASC;

Drop Table TempCalendar;

Re: date

MayilVahanan — Tue, 23 Oct 2012 16:46:28 GMT

From table viewer, you can able to see the new_orderdate field value? if so, can u able to say any one value ?

Re: date

userid128223 — Tue, 23 Oct 2012 16:58:44 GMT

I get error while reloading hence it does not get to that point.

Re: date

MayilVahanan — Tue, 23 Oct 2012 17:05:41 GMT

I think, your script are correct.. pls do one thing, comment ur master calendar, load only sales table and find whether the new_orderdate field has value or not? from that we can conclude it. and also do one thing, use in text box like this = min(orderdate), if it gives value, that its already in correct format , don't want to apply date#() function..

Hope it helps

Re: date

userid128223 — Tue, 23 Oct 2012 17:14:12 GMT

Hi Mayil

I commeted the master calendar and was able to get the value for new_orderdate.

How can I pass this new_orderdate to master calander.

Re: date

MayilVahanan — Tue, 23 Oct 2012 17:28:12 GMT

Can you post a error screen shot..

Have doubt, new_orderdate gives value means, ok .. use set instead of let.. and see in the variable, if it gives value means, new_orderdate is not a problem...

If possible, give the sample text file. will try n give the solution..

Re: date

v_iyyappan — Tue, 23 Oct 2012 17:41:16 GMT

Hi,

Try like this

[Sales Table]:

LOAD

Name,

Number,

Model,

Make,

orderdate,

Floor(Num(orderdate)) as orderdateNum

from

......... (text file)

// MASTER CALENDER

QuartersMap:

MAPPING LOAD

rowno() as Month,

'Q' & Ceil (rowno()/3) as Quarter

AUTOGENERATE (12);

Temp:

Load

min(orderdateNum) as minDate,

max(orderdateNum) as maxDate

Resident [Sales Table];

LET varMinDate = FieldValue('minDate', 1);

LET varMaxDate = FieldValue('maxDate', 1);

DROP Table Temp;

MasterCalendar:

Date(orderdateNum, 'YYYYMMDD') as Orderdate

Year(orderdateNum) As Year,

Month(orderdateNum) As Month,

Day(orderdateNum) As Day,

week(orderdateNum) As Week,

YeartoDate(orderdateNum)*-1 as CurYTDFlag,

YeartoDate(orderdateNum,-1)*-1 as LastYTDFlag,

inyear(orderdateNum, Monthstart($(varMaxDate)),-1) as RC12,

date(monthstart(orderdateNum), 'MMM-YYYY') as MonthYear,

ApplyMap('QuartersMap', month(orderdateNum), Null()) as Quarter,

Week(weekstart(orderdateNum)) & '-' & WeekYear(orderdateNum) as WeekYear,

WeekDay(orderdateNum) as WeekDay

LOAD

($(varMinDate) + (RecNo()) - 1) AS orderdateNum

AUTOGENERATE ($(varMaxDate) - $(varMinDate) + 1);

Hope its help

Iyyappan

Re: date

userid128223 — Tue, 23 Oct 2012 18:12:23 GMT

Date# is working fine and it is create a new varible, I think reading it into master table is where the issue arises.

I can see the script running up till the point where in master calendar it tries to read new_orderdate and then it fails saying field not found.

when i create a little text box =new_orderdate, I can see the date. hence

1) date is being created fine.

2) master calender it fails at this point min(new_orderdate) as minDate

It seems to me that it is referencing issue.

Re: date

userid128223 — Tue, 23 Oct 2012 18:15:20 GMT

Incoming field is a text field in below format

YYYYMMDD

1) convert text date field into proper date field. ie new_orderdate

2) call that date field into master calendar. to create various date, month, year, day, weekday fields.

Re: date

MayilVahanan — Tue, 23 Oct 2012 18:27:55 GMT

Please check the attached file.. Its work for me..

Re: date

userid128223 — Tue, 23 Oct 2012 19:27:37 GMT

Its weird I tried isolating this issue. by doing this. I get error new_orderdate field not found.

tempdate1:

Load orderdate,

Date#(orderdate,'YYYYMMDD') as new_orderdate

FROM ................

tempdate2:

load new_orderdate

resident tempdate1;

Re: date

MayilVahanan — Tue, 23 Oct 2012 19:43:04 GMT

Please check once , Date() function and tel the result.. can you?

Re: date

MayilVahanan — Tue, 23 Oct 2012 20:35:59 GMT

Just now change the format YYYYMMDD as in text file and run it..

Please check the attached file..

Re: date

userid128223 — Tue, 23 Oct 2012 21:40:45 GMT

I figured it out. Thank you guys for all your help.

I had qualify function in my script that was messing things up.