GROUP BY question, get the most recent record from a log

[eadbhard]

Hi there,

I have a table with a log of database sizes, which are taken every day. Each day, a new record goes to the log table with the following columns: database id, size of the database, size of the database log, total size (sum of the two) and the date. Log table have thousands of records:

Now I want to create a simple bar chart to show the current size of each database, that is, the size of the database taken on the most recent date on the log. X axis = database name, Y axis = current size of the database.

For this I imagined I would be able to create a new table using GROUP BY Max(SizeDate):

LastSize:
LOAD DbID as LastDbId,
    DataFileSizeMB as LastDataSize,
    LogFileSizeMB as LastLogSize,
    TotalFileSizeMB as LastTotalSize,
    Max(SizeDate) as LastDateSize
    Resident LogDatabaseSize // the whole log table
    Group by DbID, DataFileSizeMB, LogFileSizeMB, TotalFileSizeMB;

However, the new table is not showing one aggregated record per Database as I was expecting. It still got multiple records per database, for different dates:

Right now I am not sure why the GROUP BY didn't work as I expected.

I'd appreciate any help. Alternative solutions where a GROUP BY is not involved/required are also welcome and appreciated.

Thanks,

-Eduardo

[Lisa_P]

I think the group by makes sense, but you only need to load this:
LastSize:
LOAD DbID,
      Max(SizeDate) as LastDateSize
    Resident LogDatabaseSize // the whole log table
    Group by DbID;

Your table on the front end would just need

DbID, LastDateSize, TotalFileSizeMB 

[eadbhard]

Thanks. But I'm afraid it doesn't work. I did this:

LastSize:
LOAD DbID,
Max(SizeDate) as LastDateSize
Resident LogDatabaseSize
Group by DbID;

Then created a Table Box object with this three columns: DbID, LastDateSize, TotalFileSizeMB.

The resulting table still shows multiple records per database:

I really appreciate your time and help. I'd appreciate any other ideas though.

Kind regards,

-Eduardo

[Brett_Bleess]

Only thing of which I can think is you may need to set this up as a preceding load:

https://community.qlik.com/t5/Qlik-Design-Blog/Preceding-Load/ba-p/1469534

Do the Max function in the first load and the inside load just does the load and GroupBy I think, but I am not very good at the development stuff, so not sure! 🙂  

Regards,
Brett