Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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;
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;
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.