Resident load from qvd with custom date format...

[#1]

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!

[michael123 · Accepted solution]

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;

[#3]

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.