topic Re: Group by question in QlikView

Group by question

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Group by question

Anil_Babu_Samineni — Fri, 28 Apr 2017 12:17:53 GMT

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

Re: Group by question

Anonymous — Fri, 28 Apr 2017 12:20:36 GMT

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

Re: Group by question

Anonymous — Fri, 28 Apr 2017 12:20:56 GMT

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.

Re: Group by question

Anonymous — Fri, 28 Apr 2017 12:23:14 GMT

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.

Re: Group by question

Anonymous — Fri, 28 Apr 2017 12:25:51 GMT

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

Re: Group by question

senpradip007 — Fri, 28 Apr 2017 12:27:18 GMT

Could please upload some sample qvw?

Re: Group by question

Anil_Babu_Samineni — Fri, 28 Apr 2017 12:30:57 GMT

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, ...;

Re: Group by question

MindaugasBacius — Fri, 28 Apr 2017 12:45:45 GMT

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

;