how can I obtain the good count

[suzel404]

Hi all,

I have a problem with aggregation function :

LOAD * INLINE [

STORE_ID, DRT_ID_MAG, DRT_ID_CONSO, SITE_ID_MAG, SITE_ID_CONSO, DATE_MAG, DATE_CONSO
575, 74, 74, 930, 930, 12/12/2013 12:59:47, 03/12/2013 10:29:11
575, 74, 74, 930, 930, 12/12/2013 12:59:47, 11/12/2013 00:00:00

083, 84, 84, 930, 930, 31/01/2014 12:30:29, 22/01/2014 00:00:00
083, 84, 84, 930, 930, 31/01/2014 12:30:29, 22/01/2014 11:50:00
083, 84, 84, 930, 930, 31/01/2014 12:30:29, 23/01/2014 10:20:00
083, 84, 84, 930, 930, 31/01/2014 12:30:29, 31/01/2014 00:00:00
083, 84, 84, 930, 930, 31/01/2014 12:30:29, 04/02/2014 11:33:00

078, 36, 36, 930, 930, 31/01/2014 12:30:24, 22/01/2014 00:00:00
078, 36, 36, 930, 930, 31/01/2014 12:30:24, 22/01/2014 19:46:00
078, 36, 36, 930, 930, 31/01/2014 12:30:24, 23/01/2014 10:20:00
078, 36, 36, 930, 930, 31/01/2014 12:30:24, 31/01/2014 00:00:00

107, 28, 28, 930, 930, 01/02/2014 10:04:00, 23/01/2014 00:00:00
107, 28, 28, 930, 930, 01/02/2014 10:04:00, 23/01/2014 12:00:00
107, 28, 28, 930, 930, 01/02/2014 10:04:00, 24/01/2014 10:21:49
107, 28, 28, 930, 930, 01/02/2014 10:04:00, 01/02/2014 00:00:00];

I want to create a Flag in script with the condition follows :

(IF(DATE_CONSO>DATE_MAG AND DRT_ID_CONSO=DRT_ID_MAG AND SITE_ID_CONSO=SITE_ID_MAG,'KO')) as NB_KO

(IF(DATE_CONSO<DATE_MAG AND DRT_ID_CONSO=DRT_ID_MAG AND SITE_ID_CONSO=SITE_ID_MAG,'OK')) as NB_OK

I compare the last DATE_CONSO and last DATE_MAG. How can I obtain this : Exemple (if I select SITE_ID_MAG = 930)

SITE_ID_MAG ; NB_MAG ; NB_KO ; NB_OK

     930              ;      4         ;       1      ;    3

Thanks for your help.

[whiteline]

Hi.

You have to add some kind of rowid field to achieve this.

Since you use implicit aggregation in your count expressions, it runs through all the possible combinations of field values, not for every row as you want.

Use aggr function with rowid field.

[lironbaram]

HI

HAVE A look at the attach example

[suzel404]

Thanks WhiteLine & Liron.

I have an other question.

[lironbaram]

and what is the new question

[suzel404]

Thanks WhiteLine & Liron for your answers.

I have an other question.

I create a ordinary table Trends (20%,80%).

How can show the straight depending on the Trends selection.

20% or 80% of total result. If 20% & 80% are selected, the straight table shows a message.

Thanks for your help.

[lironbaram · Accepted solution]

have a look at the attach model

i changed the variable and the error message

[suzel404]

Many thanks Liron for yout time.

It works well !!

[suzel404]

Liron,

In my actual configuration, is it possible to obtain the NB_MAG (count (all distinct) using Sum.

The Sum function is more performant, right ?

Thanks.