topic Re: Insert Field when equal to other column multiple values in App Development

Insert Field when equal to other column multiple values

patriciousa — Tue, 19 Jan 2016 15:24:10 GMT

Hi Qlik Community.

I'm here today with what must be a really easy task.

I need to insert a Field but only when is equal to other column values.

Here is my table:

Nombre	Categoría
Manuel Andres	JPE
María del Mar	JPE
Gabriela Susana	Supervisor
Gustavo	Director
Christian	Senior
Damian Uriel	JP
Ramon	Gerente
Hernan	Gerente
Leandro Martin	Senior
Carlos Matias	Senior

I want to insert in my table in Qlik Sense the Column "Nombre" when "Categoría" is equal to JP, JPE and Senior.

I bet this has to be like the easiest thing in Qlik but I can't figure out how to do it.

Thank you in advance.

Regards.

Re: Insert Field when equal to other column multiple values

sunny_talwar — Tue, 19 Jan 2016 16:56:01 GMT

May be this:

LOAD Nombre,

Categoria

FROM Source

Where Match(Categoria, 'JPE', 'JP', 'Senior');

Re: Insert Field when equal to other column multiple values

patriciousa — Tue, 19 Jan 2016 16:58:45 GMT

To clarify, I need to Load all the categories, however, in the table view I'm making in Qlik Sense, I only have to show JP, JPE, Senior.

Thank you for your reply.

Re: Insert Field when equal to other column multiple values

sunny_talwar — Tue, 19 Jan 2016 17:12:48 GMT

Oh okay, may be this in that case:

{<Categoria = {'JPE', 'JP', 'Senior}>}

This will go within all your expressions.

Re: Insert Field when equal to other column multiple values

patriciousa — Tue, 19 Jan 2016 17:15:43 GMT

Thank you for quick reply Sunny-T but still need help.

The expression you gave is looking good but I need to insert the "Nombre" Column when {<Categoria = {'JPE', 'JP', 'Senior}>}

How do I do it?

Thank you again!

Re: Insert Field when equal to other column multiple values

sunny_talwar — Tue, 19 Jan 2016 17:40:14 GMT

Not entirely sure how and where you are using this, but may be this:

Only({<Categoria = {'JPE', 'JP', 'Senior}>} Nombre)

Re: Insert Field when equal to other column multiple values

patriciousa — Tue, 19 Jan 2016 17:46:18 GMT

Qlik Sense, as Dimension, in a Table.

There, where it says Colaborador JP-Seniors. I need to insert only the "nombre" when is equal to those categories.

If I paste what you gave me Only({<Categoria = {'JPE', 'JP', 'Senior}>} Nombre) it says Error in Expression.

Try adding a , before Nombre, didn't work.

The Field "Nombre" is not being recognized as a Field.

Thank you.

Regards.

Re: Insert Field when equal to other column multiple values

sunny_talwar — Tue, 19 Jan 2016 18:38:04 GMT

When using it as a dimension you need an Aggr function, try this:

=Aggr(Only({<Categoria = {'JPE', 'JP', 'Senior}>} Nombre), Nombre)

Alternatively this should also work:

=If(Match(Categoria, 'JPE', 'JP', 'Senior), Nombre)

But my suggestion is that you can get the same result by using set analysis in all your expressions. You just need to use Nombre as dimension and then within the expression use {<Categoria = {'JPE', 'JP', 'Senior}>}

For example if your current expression is -> Sum(Sales), change it to Sum({<Categoria = {'JPE', 'JP', 'Senior}>}Sales) Your Nombre will only show those names which are from one of the three Categoria listed in the set analysis. This is more efficient then using calculated dimension.

Re: Insert Field when equal to other column multiple values

patriciousa — Tue, 19 Jan 2016 19:06:53 GMT

You got it! It worked!

Thank you a lot, again!

Regards!

Re: Insert Field when equal to other column multiple values

sunny_talwar — Tue, 19 Jan 2016 19:14:49 GMT

Hahahaha, More like 'We got it'

Re: Insert Field when equal to other column multiple values

patriciousa — Fri, 12 Feb 2016 20:06:19 GMT

Hi Sunny. Can I ask you something... If I want to use the Set Analysis to all my expressions using {<Categoria = {'JPE', 'JP', 'Senior}>}.

How do I do it?

Thank you again.

Regards.

Re: Insert Field when equal to other column multiple values

sunny_talwar — Fri, 12 Feb 2016 21:23:08 GMT

All your expressions? You can create a variable:

vVar = {<Categoria = {'JPE', 'JP', 'Senior}>}

and then use it something like this -> Sum($(vVar) Sales) or Sum($(=vVar) Sales)

The expression editor might show this as error, but I think one of the two should work.

Re: Insert Field when equal to other column multiple values

patriciousa — Fri, 12 Feb 2016 22:25:34 GMT

My bad. I wasn't clear enough.

I have a sheet with tables and a bart chart. How can I apply this filter to the entire sheet?

Is it possible?

Thank you.

Regards.