Problem using month

Mon, 25 Oct 2010 15:03:56 GMT

Hello,

From my sctipt:

Pre_Fakta:
LOAD FOKID,
FTGID,
KundNr,
AnnulleradOrder_Uppläggningsdatum as Date,
AnnulleradOrder_OrdNr,
4 as Source
FROM $(include=path.txt)AnnOrd.qvd (qvd);

Concatenate(Pre_Fakta)
LOAD FOKID,
FTGID,
KundNr,
Uppläggningsdatum as Date,
OrderNrKvar,
OrderNrIn,
1 as Source
FROM $(include=path.txt)Order.qvd (qvd);

Fakta:
NOCONCATENATE LOAD *
RESIDENT Pre_Fakta
ORDER BY Date ASC;

Drop table Pre_Fakta;

TempOrder:
LOAD max(Date) as MaxDate,
min(Date) as MinDate
RESIDENT Fakta;

LET varMinDate = Num(Peek('MinDate', 0, 'TempOrder'));
//LET varMaxDate = Num(Peek('MaxDate', 0, 'TempOrder'));
LET varMaxDate = num(today());
LET varToday = num(today());

Drop Table TempOrder;

//*************** Temporary Calendar ***************
TempCalendar:
LOAD
$(varMinDate) + rowno() - 1 AS Num,
date($(varMinDate) + rowno() - 1) AS TempDate
AUTOGENERATE
$(varMaxDate) - $(varMinDate) + 1;

//*************** Master Calendar ***************
MasterCalendar:
LOAD TempDate AS Date,
Week(TempDate) AS Week,
Year(TempDate) AS Year,
Month(TempDate) AS Month,
Day(TempDate) AS Day,
Weekday(TempDate) AS WeekDay,
'Q' & ceil(month(TempDate / 3)) AS Quarter,
Date(monthstart(TempDate), 'MMM-YYYY') AS MonthYear,
Week(TempDate)&'-'&Year(TempDate) AS WeekYear,
inyeartodate(TempDate, $(varToday), 0) * -1 AS CurYTDFlag,
inyeartodate(TempDate, $(varToday), -1) * -1 AS LastYTDFlag

RESIDENT TempCalendar
ORDER BY TempDate ASC;

DROP TABLE TempCalendar;

My problem is that Month, Week, Day, Year aso (created in the MasterCalender) only has values when the row originate from Order.qvd.
All rows from AnnOrd.qvd have Date but not Week, Month Year Day aso are missing.

Anyone that can enlighten me and tell me what i am doing wrong?
They are stored in the same table with the same function and I can see that all rows in the AnnOrd.qvd file are within the MaxDate and MinDate.

br
Martin

Problem using month

marcel_olmo — Thu, 28 Oct 2010 00:43:15 GMT

I don't get why you need to create this temp Calendar, and then a master Calendar. There is an easier way.

If I were you, I would do the following :

Pre_Fakta :

Load ...

Date

from annOrd.qvd;

concatenate(Pre_Fakta)

Pre_Fakta :

Load ...

Date

from Order.qvd;

And then what changes is to do directly a master calendar loading resident tables of AnnOrd.qvd and Order.qvd , and also you're only gonna get the available dates (because I think it has not sense to show a date with non related values) :

MasterCalendar :

Load date,

year(date) as year,

...

resident Pre_Fakta;

Hope it helps!!

topic Problem using month in QlikView

Problem using month

Problem using month