topic Re: Group By in QlikView

Group By

pascos88 — Mon, 01 Feb 2016 14:27:31 GMT

Hi all,

for Each Value in '[ID1]','[ID2]','[ID3]','[ID4]','[ID5]','[ID6]','[ID7]','[ID8]','[ID9]','[ID10]'

MAP:

LOAD

date(Datum,'DD/MM/YYYY') as Data,

Time(Zeit,'hh.mm.ss') as Hour,

Production,

Theoretical,

Budget,

replace(replace('$(Value)','[',''),']','') as NomeCompany

FROM

[$(vSourceXLS)Smart_PV_App.xlsx]

(ooxml, embedded labels, table is $(Value))

Group By Zeit,Production,Theoretical, Budget, NomeCompany;

what is wrong in a group by?

I need to group for Data

thanks for any help

Pasquale

Re: Group By

sunny_talwar — Mon, 01 Feb 2016 14:29:14 GMT

I don't think you need the Group By Statement here. There is not aggregation going on in your load (Sum, Avg, Only, Concat, Max, or Min)

FOR each Value in '[ID1]','[ID2]','[ID3]','[ID4]','[ID5]','[ID6]','[ID7]','[ID8]','[ID9]','[ID10]'

MAP:

LOAD Date(Datum,'DD/MM/YYYY') as Data,

Time(Zeit,'hh.mm.ss') as Hour,

Production,

Theoretical,

Budget,

Replace(Replace('$(Value)','[',''),']','') as NomeCompany

FROM

[$(vSourceXLS)Smart_PV_App.xlsx]

(ooxml, embedded labels, table is $(Value));

Next

Re: Group By

Anonymous — Mon, 01 Feb 2016 14:32:28 GMT

Would you need to use your dollar expansion instead of NomeCompany ?

for Each Value in '[ID1]','[ID2]','[ID3]','[ID4]','[ID5]','[ID6]','[ID7]','[ID8]','[ID9]','[ID10]'

MAP:

LOAD

date(Datum,'DD/MM/YYYY') as Data,

Time(Zeit,'hh.mm.ss') as Hour,

Production,

Theoretical,

Budget,

replace(replace('$(Value)','[',''),']','') as NomeCompany

FROM

[$(vSourceXLS)Smart_PV_App.xlsx]

(ooxml, embedded labels, table is $(Value))

Group By Zeit,Production,Theoretical, Budget, '$(Value)' ;

Next

Also you need an aggregation function round any field not in the Group By clause.

Re: Group By

pascos88 — Mon, 01 Feb 2016 14:38:34 GMT

Hi Sunny,

thanks for the responce.

I have a lot of same Data field. I need to have a view of just one of this.

Data Hour Value

01/01/2015 00.00 11

01/01/2015 00.15 55

01/01/2015 00.30 44

01/01/2015 01.00 78

01/01/2015 01.15 78

I would like to be able to have in a list box one time 01/01/2015 and in another list box the possibility to chose the hour.

Can you halp me in this?

thanks

Re: Group By

sunny_talwar — Mon, 01 Feb 2016 14:48:14 GMT

Not sure I understand. You want to be able to select a date and then select what? the hour? Isn't that working for you right now?

Re: Group By

pascos88 — Mon, 01 Feb 2016 15:08:00 GMT

I find a problem Sunny, the format of my data (from excel) is 03/01/2015 00:45:00 in the same cell.

So in qlikview I read the data also with hour.

Do you know how from this format (03/01/2015 00:45:00 ), I can take in qv just th format 03/01/2015 without hour?

Regards

Pasquale

Re: Group By

sunny_talwar — Mon, 01 Feb 2016 15:15:51 GMT

Try like this:

Date(Floor(TimeStamp#(YourDateField, 'MM/DD/YYYY hh:mm:ss'))) as Date

Here I am assuming 03/01/2015 = March 1st 2015. if it is Jan 3rd 2015 then change the MM/DD/YYY in the above expression to DD/MM/YYYY

For extracting time, you can do this:

Time(Frac(TimeStamp#(YourDateField, 'MM/DD/YYYY hh:mm:ss'))) as Time