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 😕

[Peter_Cammaert]

I do not understand this case, sorry. However, I cn try to explain why you're getting an error:

Your concat() is an aggregation function and NOT a simple string concatenation (the & operator does that job). On the other hand, JD_CODUNIQ and JS_CODE_REP are not allowed in that spot without aggregation functions because these fields don't appear in the GROUP BY clause.

Best,

Peter

[#3]

Well the thing is that I need to concatenate the JD_NDEVIS with 'v' and with the max of JD_NVARIANT (per JD_NDEVIS)

After that I need to lookup for this value I just created and get the JD_CODE_REP corresponding

[#4]

So here is how my table looks liker right now:

As you can see, for the same order, I have two customers and three commercial. Since the last quote of the order correspond to the comercial 'RS' and customer 'SPN', I would like that the other quotes go under that line

[#5]

This is how it looks like in excel and how it has to work:

[jonathandienst]

Hi

I would do it like this:

// Build mapping table to look up the value of JD_CODE_REP

MapCodeRep:

Mapping LOAD

  JD_CODUNIQ,

  JD_CODE_REP

resident <some table>;   // this is the table containing the above two fields

// Now load the data and apply the map to get the correct JD_CODE_REP value

Data:

LOAD *,

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

resident Table_suivi_devis

group by JD_NDEVIS;

ApplyMap id the QV equivalent of vlookup or hlookup in Excel.

HTH

Jonathan

[#7]

This is what I need but there is a small error, it tells me this

Fields name must be unique in table

Data:

LOAD *,

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

resident Table_suivi_devis

group by JD_NDEVIS

I don't see what the problem is 😕

[Peter_Cammaert]

Most probably the * specifier in your LOAD statement already pulls in a field called JD_CODE_REP. Check the source table Table_suivi_devis, or simply use a different name after AS.

To be sure, put keyword NOCONCATENATE before the LOAD statement (after the table label)

Peter

Edit: sorry, noconcatenate only needed for good measure, not to fix the error.

[#9]

Here is how I load the data from the database:

Table_suivi_devis:

LOAD ENDV_REPRESENTANT as JD_CODE_REP, etc...

than I load it for the mapping and again for the applymap

[Peter_Cammaert]

If in the last LOAD you want to replace the original JD_CODE_REP with the new one, you must replace the * symbol in the last LOAD with a full field list from the original table Table_suivi_devis MINUS the original JD_CODE_REP column.

Otherwise you will be creating two columns with an identical name, which is not allowed in most query languages.

Best,

Peter