Group by question

[Anonymous]

Here is what i am loading. I am having to load in several files. I need to group by FileDate but not sure how.

LOAD

Date(Date#(Left(FileBaseName(), 10), 'YYYY-MM-DD')) As FileDate,

Vehicle,

Division,

Region,

[Total Stops],

[Hard Accel],

[Harsh Brake],

[Speeding > 10 mph]

Seatbelt,

[Total Distance Miles]/ ([Speeding > 10 mph] +      [Harsh Brake] + Seatbelt +      [Hard Accel]) as Safety

FROM

$(File)

(ooxml, embedded labels, header is 12 lines, table is [Sheet 1]);

next File

[Anil_Babu_Samineni]

I am assuming, you don't require Group by over here. Can i know the reason as you need this.

[Anonymous]

If you want to able to find the data from each file,

LOAD

Date(Date#(Left(FileBaseName(), 10), 'YYYY-MM-DD')) As FileDate,

Vehicle,

Division,

Region,

[Total Stops],

[Hard Accel],

[Harsh Brake],

[Speeding > 10 mph]

Seatbelt,

[Total Distance Miles]/ ([Speeding > 10 mph] +      [Harsh Brake] + Seatbelt +      [Hard Accel]) as Safety,

Filename() as Filename

FROM

$(File)

(ooxml, embedded labels, header is 12 lines, table is [Sheet 1]);

next File

[Anonymous]

I do need group by because when I look at the data I am getting more than one Safety calculation for a single region for the specified FileDate.

But I am not sure how to get group by to work in this load so that i why I was asking here.

[Anonymous]

I already know how to find the date from each file.  FileDate gives me the date from the files.  But I am getting more than one safety calculation for a single region for the specified FileDate so I am trying to find out how to group by FileDate so that I can get a grid chart to work properly.

[Anonymous]

Something like this ??

LOAD

Date(Date#(Left(FileBaseName(), 10), 'YYYY-MM-DD')) As FileDate,

Vehicle,

Division,

Region,

sum([Total Stops]),

sum([Hard Accel]),

sum([Harsh Brake]),

sum([Speeding > 10 mph]),

sum(Seatbelt),

sum([Total Distance Miles])/ sum([Speeding > 10 mph] +      [Harsh Brake] + Seatbelt +      [Hard Accel]) as Safety

FROM

$(File)

(ooxml, embedded labels, header is 12 lines, table is [Sheet 1])

group by

Vehicle,

Division,

Region

next File

[senpradip007]

Could please upload some sample qvw?

[Anil_Babu_Samineni]

But, Without aggregation i don't think group by run on fly. But, You can try something like below

LOAD

Date(Date#(Left(FileBaseName(), 10), 'YYYY-MM-DD')) As FileDate,

Vehicle,

Division,

Region,

[Total Stops],

[Hard Accel],

[Harsh Brake],

[Speeding > 10 mph]

Seatbelt,

Sum([Total Distance Miles])/ RangeSum([Speeding > 10 mph] +      [Harsh Brake] + Seatbelt +      [Hard Accel]) as Safety

FROM

$(File)

(ooxml, embedded labels, header is 12 lines, table is [Sheet 1]) Group By FileDate, ...;

[MindaugasBacius · Accepted solution]

Group by is needed when you are doing an aggregation in the Load statement.

By aggregation I mean you are willing to get Min, Max, Sum, Count information of the data.

For example:

You have a Table with 3 fields Date, Product, Sales.

You need to get total sum per Date then:

Load Date

     ,Sum(Sales) as [Total Sales]

Resident Table

Group By Date

;