topic Re: Lookup and concat in QlikView

Lookup and concat

Thu, 09 Oct 2014 11:38:07 GMT

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 😕

Re: Lookup and concat

Peter_Cammaert — Thu, 09 Oct 2014 12:26:36 GMT

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

Re: Lookup and concat

Thu, 09 Oct 2014 12:32:51 GMT

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

Re: Lookup and concat

Thu, 09 Oct 2014 12:37:21 GMT

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

Re: Lookup and concat

Thu, 09 Oct 2014 13:27:49 GMT

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

Re: Lookup and concat

jonathandienst — Thu, 09 Oct 2014 13:39:31 GMT

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

Re: Lookup and concat

Thu, 09 Oct 2014 13:51:32 GMT

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 😕

Re: Lookup and concat

Peter_Cammaert — Thu, 09 Oct 2014 14:21:04 GMT

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.

Re: Lookup and concat

Thu, 09 Oct 2014 14:31:25 GMT

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

Re: Lookup and concat

Peter_Cammaert — Thu, 09 Oct 2014 14:37:13 GMT

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

Re: Re: Lookup and concat

Thu, 09 Oct 2014 14:50:27 GMT

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

Re: Lookup and concat

Peter_Cammaert — Thu, 09 Oct 2014 15:13:55 GMT

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;

Re: Re: Lookup and concat

Fri, 10 Oct 2014 07:33:34 GMT

Works perfectly Thank you very much!!