Group By

[pascos88]

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;

Next

what is wrong in a group by?

I need to group for Data

thanks for any help

Pasquale

[sunny_talwar]

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

[Anonymous]

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.

[pascos88]

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

[sunny_talwar]

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?

[pascos88]

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

[sunny_talwar · Accepted solution]

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