Max of concatenation

[#1]

Hello,

For an analysis I need to retrieve the max date of some datas. Let me explain:

The company is issuing differen quotes for the same order number. Therefore they asked me to retrieve some statistics starting from august 01, 2014. The problem is that some quotes for an order have been issued after that date and before that date. Until now, my script was retrieving only  the quotes that were issued after 01/08/2014, but the quotes issued before are forgotten but very important for my statistics.

After loading the datas for the quotes and orders, I tried to load the quotes on another way, like this:

Load JD_NVARIANT,

       max(concat(JD_DATE)) as JD_DATE_MAX

resident Table_suivi_devis

group by JD_NVDEVIS;

where JD_NVARIANT is the quote number, JD_DATE, the date of the quote and JD_NDEVIS, the order number. When relaoding, an error appear: Nested aggregation not allowed

How could I solve this problem? Thank you

[rubenmarin]

Hi Vincent,

You need to group by all fields that aren't agregated, so JD_NVARIANT has to be part of the 'group by':

Load JD_NVARIANT,

       max(concat(JD_DATE)) as JD_DATE_MAX

resident Table_suivi_devis

group by JD_NVARIANT, JD_NVDEVIS;

Also, I don't understand the "max(concat(JD_DATE))"?

Probably should be only max(JD_DATE)

[#3]

I tried this:

Load JD_NVARIANT,

       max(concat(JD_DATE)) as JD_DATE_MAX

resident Table_suivi_devis

group by JD_NVARIANT, JD_NVDEVIS;

and this:

Load JD_NVARIANT, JD_NDEVIS,

       max(concat(JD_DATE)) as JD_DATE_MAX

resident Table_suivi_devis

group by JD_NVARIANT, JD_NVDEVIS;

But it still tells me the same error 😕

[rubenmarin]

Should be the max(concat(JD_DATE)), try only max(JD_DATE)

[#5]

There is no error with this, but it doesn't filter the data correctly 😕

In the beginning, I tried something like this, because it is what it should look like:

Load JD_NVARIANT,

if(max(concat(JD_DATE)) >= '01/08/2014', JD_DATE_OK)

resident Table_suivi_devis

group by JD_NVARIANT, JD_NVDEVIS;

but I knew it would be working since the true value is not good. Do you think of qnother way of doing so?

[rubenmarin]

concat makes a concatenation of strings, so if one date is '01/08/2014' and another '02/08/2014' it will return '01/08/201402/08/2014'

Can you try?:

if(max(Date(JD_DATE))>= '01/08/2014', JD_DATE_OK)

If you can upload a sample and what you're expecting I can give you a better help.

[#7]

Since my field JD_DATE_OK doesn't exist yet, I transformed a little bit your expression:

  if(max(JD_DATE)>= '01/08/2014', JD_DATE) as JD_DATE_OK

but there is an error 😕

I aslo tried something like that:

  if(max(JD_DATE)>= '01/08/2014', 1, 0) as DATE_OK

so when I filter my data it only show the order numbers that have a quote issued after the date 01/08/2014 but I still have some orders not corresponding to the condition.

[rubenmarin]

But all the quote dates are >='01/08/2014'?

When you create a field like:

max(JD_DATE) as MAX_JD_DATE

What values are stored in MAX_JD_DATE?

[#9]

In order to show the order, only one quote has to have a date as >='01/08/2014'. If this requirement is met, all the quotes (even the one with a date <'01/08/2014') can be retrieved.

I cretaed a field like max(JD_DATE) as MAX_JD_DATE and it looks like this:

[#10 · Accepted solution]

Well actually in my table statistics it is working good.

Here is the script used:

Load JD_NDEVIS,

       if(max(date(JD_DATE))>='01/08/2014',1,0) as DATE_OK,

       max(date(JD_DATE)) as JD_DATE_OK

resident Table_suivi_devis

group by JD_NDEVIS;

and in the dimension expression of JD_NDEVIS, I used this:

=if(DATE_OK=(1), JD_NDEVIS)