topic Need a hand with aggregation table in QlikView

Need a hand with aggregation table

Fri, 23 Apr 2010 08:31:54 GMT

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?

Need a hand with aggregation table

Fri, 23 Apr 2010 08:49:43 GMT

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

Need a hand with aggregation table

Fri, 23 Apr 2010 08:54:10 GMT

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?

Need a hand with aggregation table

Fri, 23 Apr 2010 08:56:20 GMT

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).

Need a hand with aggregation table

Fri, 23 Apr 2010 12:32:06 GMT

Anyone have a clue yet ?

Need a hand with aggregation table

Fri, 23 Apr 2010 13:59:40 GMT

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.