Lookup and concat

[#1]

Hello, here is my little problem:

For an order/quote analysis, I need to sort my quotes by commercial, customer and then order. So each quote has its own commercial, customer and finally order number. Until now, it worked perfectly until I met an order with different quotes that change of commercial over time. So two quotes where for commercial A and three quotes where for commercial B while the order number is the same.

I would like to have the information of all the quotes but sort the order by the commercial and customer that are written on the last quote.

For the information, each quote has a unique code made up of the order number and the number of the quote: le say order is 105236 nd quote number 4, the unique code will look ike this: 105236v4

Therefore I came up with the idea of using the lookup function like in excel:

LOAD *,

lookup(concat(JD_NDEVIS&'v''max(JD_VARIANT)), JD_CODUNIQ, JD_CODE_REP

resident Table_suivi_devis

group by JD_NDEVIS;

where JD_NDEVIS: order number

JD_NVARIANT: quote number

JD_CODUNQ: unique code of the quote

JD_CODE_REP: commercial

but it tells me that there is nested aggregation or seomthing like that 😕

[#2]

I tried by adding all the fields, but it still tells me there is an error 😕

I will join the script (first tab) if you find it easier

[Peter_Cammaert · Accepted solution]

Sorry, forget my last remark, I was missing the point. Back to my first response: if you use a GROUP BY clause, you can only LOAD fields without aggregation function if those same fields appear in the GROUP BY clause. All others can only be listed as parameter of an aggregation function.

Now with respect to your code, I suggest you replace the entire code block for table Data: (including its label, we won't need it anymore) by this:

DROP Field JD_CODE_REP;

LEFT JOIN (Table_suivi_devis)

LOAD JD_NEVIS,

           ApplyMap('MapCodeRep', JD_NDEVIS & 'v' & max(JD_NVARIANT)) As JD_CODE_REP

RESIDENT Table_suivi_devis

GROUP BY JD_NDEVIS;

[#4]

Works perfectly Thank you very much!!