Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
eduardo_dimperio
Specialist II
Specialist II

Help with Group By

Hi Everyone,

I have a litle problem here. I have some values by hour and i need to get the lower hour (by hour)

Like

Input

1:00 am --- 100

1:03 am --- 120

1:50 am --- 190

2:02 am --- 200

2:27 am --- 230

2:45 am --- 280

output

1:00 am --- 100

2:02 am --- 200

I have this code:

LOAD

    DATE_READ,

    VALUE_READ,

    ID_SYSTEM&'|'&CONCENTRATOR&'|'&PORT AS ID_LEITURA

RESIDENT CONCAT_TABLE where DATE_READ>=TODAY()-2;

And tried this one, withou sucess.

LOAD

    DATE_READ,

    VALUE_READ,

    MIN(DATE_READ) AS MENOR_VALOR

RESIDENT CONCAT_TABLE where DATE_READ>=TODAY()-2

GROUP BY DATE_READ,VALUE_READ;

Someone could help me?

Thanks

1 Solution

Accepted Solutions
marcus_sommer

Without the use of the floor/frac-function remain these fields timestamps like 42767,5422128472 for 01.02.2017  13:00:47 and date/time will just format it. Therefore try this:

Output:

load

Date(floor(DATE_READ)) AS DATA,

Hour(DATE_READ) AS HORA,

Time(frac(DATE_READ)) AS TEMPO,

VALUE_READ resident CONCAT_TABLE

where DATE_READ>=TODAY()-2;

inner join (Output)

load

DATA,

HORA,

time(min(TEMPO)) as TEMPO

resident Output group by DATA, HORA;

DROP TABLE CONCAT_TABLE;

Exit Script;

- Marcus

View solution in original post

16 Replies
sunny_talwar

What is the error you get when you use the script?

LOAD

    DATE_READ,

    VALUE_READ,

    MIN(DATE_READ) AS MENOR_VALOR

RESIDENT CONCAT_TABLE where DATE_READ>=TODAY()-2

GROUP BY DATE_READ,VALUE_READ;

marcus_sommer

You could try something like this:

Output:

load

     date(floor(DATE_READ)) as Date, time(frac(DATE_READ)) as Time,

     hour(frac(DATE_READ)) as Hour, VALUE_READ

RESIDENT CONCAT_TABLE where DATE_READ>=TODAY()-2;

inner join (Output)

load Date, Hour, min(VALUE_READ) as MinVALUE_READ

resident Output group by Date, Hour;

- Marcus

eduardo_dimperio
Specialist II
Specialist II
Author

Hi Sunny!

I have no error message, but the output is the same as input. I thought the output would be (in that print) only 00:06:29Data_Erro.JPG

eduardo_dimperio
Specialist II
Specialist II
Author

Hi Marcus,

Thanks for your time !

I need the min Date group by hour and not value read. Sometimes, my measure 1:00 could be higher that 1:05

sunny_talwar

Look at Marcus's response below... I think that should resolve your issue

marcus_sommer

Then try it with some slight adjustements, something like this:

Output:

load

     date(floor(DATE_READ)) as Date, time(frac(DATE_READ)) as Time,

     hour(frac(DATE_READ)) as Hour, VALUE_READ

RESIDENT CONCAT_TABLE where DATE_READ>=TODAY()-2;

inner join (Output)

load Date, Hour, min(Time) as MinTime

resident Output group by Date, Hour;

- Marcus

eduardo_dimperio
Specialist II
Specialist II
Author

Hi Marcus,

Unfortunately not worked, so i tried this code:

Input:

load

Min(VALUE_READ),

FLOOR(DATE_READ,0.1)

//     ID_SYSTEM&'|'&CONCENTRATOR&'|'&PORT AS ID_LEITURA,

RESIDENT CONCAT_TABLE

  where DATE_READ>=TODAY()-2

     group by DATE_READ;

with this result

Data_Erro.JPG

And i dont understant why min(value_read), doesn't get only the small value per date

eduardo_dimperio
Specialist II
Specialist II
Author

I did another code, more clean i think

Input:

load

VALUE_READ,

FLOOR(DATE_READ,000.1) AS DATA

RESIDENT CONCAT_TABLE

  where DATE_READ>=TODAY()-2;

  

Output:

load

DISTINCT

MIN(VALUE_READ),

DATA

RESIDENT Input

    group by DATA;

DROP TABLE Input;

I thought that my code would group by date and for each one pick the smallest value, but in the end it just picked the smallest value of all dates and replied

Data_Erro.JPG

marcus_sommer

There are another slight adjustments necessary regarding to the format and the field-name of the min(Time):

Output:

load

    today() as Date, time(time#(subfield(Time, ' ', 1), 'hh:mm')) as Time,

    hour(time#(subfield(Time, ' ', 1), 'hh:mm')) as Hour, Value, Time as TimeOrigin

inline [

Time, Value

1:00 am, 100

1:03 am, 120

1:50 am, 190

2:02 am, 200

2:27 am, 230

2:45 am, 280

];

inner join (Output)

load Date, Hour, time(min(Time)) as Time

resident Output group by Date, Hour;

The inline-statement is only to have data for it and you won't need it in your load - only the methods to split the timestamp per frac() and floor() and then formatting them (and use only a pure floor() without a second parameter like 0.1 which won't create a date else just round the timestamp).

- Marcus