topic Re: Resident load from qvd with custom date format... in QlikView

Resident load from qvd with custom date format...

Wed, 27 Jun 2012 08:27:28 GMT

Hi !

First I'm loading an array of monthly qvd files, which works :

Source_table:

LOAD * From Report_*.qvd(qvd);

These qvd files have a field called Fiscal_Period, which shows the months and years in such way : 001.2010

Now I would like to make a calendar out of this, but it doesnt work :

Source_table:
LOAD * From Report_*.qvd(qvd);

LOAD Fiscal_Period,
MakeDate(Num(Right(Fiscal_Period,4)), Num(Mid(Fiscal_Period,2,2)),1) as OrderDate,
Date#(Orderdate, 'YYMM') as FinalDate
Resident Source_table

It gives the error : OrderDate not found,

What do I do wrong?

Thanks!

Re: Resident load from qvd with custom date format...

michael123 — Wed, 27 Jun 2012 09:13:46 GMT

You need to do a preceeding load. You can't reference a field created in the same load.

Load

Date#(OrderDate, 'YYMM') as FinalDate;

LOAD

Fiscal_Period,

MakeDate(Num(Right(Fiscal_Period,4)), Num(Mid(Fiscal_Period,2,2)),1) as OrderDate

Resident Source_table;

Re: Resident load from qvd with custom date format...

Wed, 27 Jun 2012 09:14:38 GMT

Hi, I found it by myself, I have to declare the OrderDate within my LOAD statement of the QVD files, otherwise it doesn't recognise the name.

Source_table:
LOAD *,

MakeDate(Left(Fiscal_Period,4),Right(Fiscal_Period,3),) as Date

From Report_*.qvd(qvd);

TempMinMaxDates:
LOAD
Min(Date) as MinDate,
Max(Date) as MaxDate
Resident Source_table;

so this works.