Need a hand with aggregation table

[#1]

Hi,

I'm having some trouble with my aggregation table. I'm fairly new to QLikview, so perhaps I'm missing something.

I have a time-table which basicly has the 'last day of the month'.
[tLASTDAYOFMONTH]:
LastDayOfMonth, Month, Year

I also have a second table with which I want to join
[tINFOTABLE]:
ObjectID,
ValidFrom_DT,
ValidTo_DT,
ObjectType,
NumberOfObjects,

Now I've tried the following:

 for each LastDayOfMonth in [tLASTDAYOFMONTH]
AggregationTable:
LOAD
 ObjectType,
 SUM ([NumberOfObjects]) AS [His.NumberOfObjects],
RESIDENT [tINFOTABLE]
WHERE
 ValidFrom_DT< LastDayOfMonth
 AND ValidTo_DT=> LastDayOfMonth
GROUP BY LastDayOfMonth , ObjectType;
next

Error: Cannot Find LastDayOfMonth

I've also tried:

LOAD
 LastDayOfMonth
 RESIDENT [tLASTDAYOFMONTH];
 JOIN
LOAD etc. etc.

Same error. What am I doing wrong?

[#2]

I think you should erase LastDayOfMonth from your group by clause or, if you will need to have the LastDayOfMonth info in your table, add it in the LOAD part.

Regards

[#3]

I removed it from the 'Group By' as you sugested.

But I'm confused about what you said about adding it to the 'LOAD' part. Isn;t it already part of the load part? What do you mean?

[#4]

I must add that the 'LastDayOfMonth' must be in the target table; Else I won't be able to produce a proper historic graph. (I'm converting the validFromDT-ValidToDT to a 'StatusOnDT' (LastDayOfMonthDT).

[#5]

Anyone have a clue yet ?

[#6]

Hi,

I think what is being referred to as including LastDayOfMonth in the LOAD statement is that you are looking to group against a column that is not being included in the query ... this woul dbe like trying to group on a field that is not included in a SELECT statement in SQL.

AggregationTable:
LOAD
ObjectType,
SUM ([NumberOfObjects]) AS [His.NumberOfObjects],

LastDayOfMonth

RESIDENT [tINFOTABLE]
WHERE
ValidFrom_DT< LastDayOfMonth
AND ValidTo_DT=> LastDayOfMonth
GROUP BY LastDayOfMonth , ObjectType;

I am not following your data structure completely but this is essentially what you would need to, I think.