topic Help with multiple choise field in QlikView

Help with multiple choise field

Thu, 14 Jul 2011 10:03:06 GMT

I hava a field in the database where the user can choose from multiple alternatives.

Example:

Field:

Alt 1

Alt 2

Alt 3

The user can now choose from the alternatives in the box and the choise is saved in the database in same field with the seperatert ;

Example: ;Alt 1;Alt 3;

Lets say that a project table have this multiple field. And now users create projects and choose differnt combinations for each project.

If i load this infomration into QV it will make one alternativ for each uniqe combination. So if i dispolat this field in a QV document it will look something like this:

;Alt 1;

;Alt 1;Alt 2;

;Alt 1; Alt 3;

;Alt 2; Alt3;

;Alt3;

Now i want to know how many projects is Alt 1 represented in. If i choose Alt 1 in the field above it will only show me project where ONLY Alt 1 is chosen. But i have other project where Alt 1 is on of many multiple chooises.

I'll hope that somebody could help mw with this. If i have explained it to bad dont hesitate to ask for further information.

Thanks!

I would now like to

Help with multiple choise field

swuehl — Thu, 14 Jul 2011 11:24:53 GMT

Hi samir,

you might want to take a look at the conditional functions, especially match / mixmatch / wildmatch.

maybe something like

count(if(wildmatch (projectsfield, '*' & 'Alt 1' & '*')>0,1))

Stefan

Help with multiple choise field

Thu, 14 Jul 2011 11:30:23 GMT

But that only solves the count in the diagram. I would like to make a Field object where the user can choose from the alternatives:

Alt 1

Alt 2

Alt 3

Maybe i didnt understand you correct, if so could you explain more detailed?

Help with multiple choise field

swuehl — Thu, 14 Jul 2011 11:30:53 GMT

Alternatively, you could probably use a set expression like

count ({<projectsfield = {"*Alt 1*"}>} projectsfield)

Re: Help with multiple choise field

lironbaram — Thu, 14 Jul 2011 11:39:46 GMT

hei

attach is an example

using the subfield function in the load script

you should note that it creates more then one record for project

so this table sholud be conected to the project table and not join

hope its helps

Re: Help with multiple choise field

swuehl — Thu, 14 Jul 2011 11:45:40 GMT

Thanks for the example, I think that helps.

Still I don't understand your requirement.

If a add a list box with field ProjectCategory to the sheet, do I have a field object like you desire to choose Category from?

And what measure do you want to be calculated at what place (e.g. new table column or text box)?

Sorry If I am a bit slow today.

Stefan

Re: Help with multiple choise field

swuehl — Thu, 14 Jul 2011 11:59:30 GMT

Maybe something like this?

I removed the subfield and added a table with just projectCategory manually (you could probably get it from your DB easily).

Then added mentioned list box and a chart table with expression like mentioned before.

Cheers,

Stefan

Help with multiple choise field

Thu, 14 Jul 2011 13:56:40 GMT

Look at the picture.

As you se i have each uniqe combination.

What i would like tho show is:

Alt 1:

Alt 2:

Alt 3:

and when pressing for example alt 1 i would like to show the sum(value) for every project where Alt 1 are presented.

Re: Help with multiple choise field

swuehl — Thu, 14 Jul 2011 14:10:17 GMT

Hi samir,

thought I was close...

Hm, just added some values to my data and prettied up the charts.

Maybe like attached sample app? (Still not sure what you are after)

It might be an idea to get rid of the concattenated strings and use a many to many linkage table instead (but that's probably not the problem we are talking about).

Regards,

Stefan

Re: Help with multiple choise field

swuehl — Thu, 14 Jul 2011 14:18:40 GMT

Me again,

if you don't like my inlined ProjectCategory table, you might use

Category:

Load Distinct

SubField(CategoryMix,';') as ProjectCategory

resident Project;

instead.

Regards,

Stefan

Re: Help with multiple choise field

Mon, 18 Jul 2011 09:07:19 GMT

I have look inte your solutions and we are getting close but not close enough

Now when i choose Alt 1 it will show me costs in 3 different bars.

I would like it to show only one bar and there it will sum all values where Alt 1 is presented. So intead of 3 bars (Alt 1, Alt1;Alt2;Alt3 and Alt1;Alt3)

Dont know if I maybe explanin it to bad for you to understand.

But a real life situation would be.

Company is selleing 3 products in different projects. In each project there could be mixes of these 3 products.

PROJECT PRODUCT

Project 1 Prod1; Prod 2

Project 2 Prod 3

Project 3 Prod 2; Prod 3

Now i want a box in QV where I can choses from the three different products.

I want a to count the number of projects where the product is presented.

Exmaple:

Press Prod 1 --> Result: 1

Press Prod 2 --> Result: 2

Press Prod 3 --> Result: 2

So i want to count how many times is the choosen product presented in a project.

Help with multiple choise field

swuehl — Mon, 18 Jul 2011 10:26:03 GMT

Hi,

maybe like this (for example put complete below expression as expression in a text box):

='Selecting Category '& concat(ProjectCategory,' & ') & chr(10) &

' results in '

& count(aggr( if(WildMatch(CategoryMix,'*'&ProjectCategory&'*')>0,1 ) ,Project))

& ' projects'

Regards,

Stefan

P.S: Again, it would also be possible to link the ProjectCategory to the Data with an additional table, to decide what is better, it depends what you want to achieve

Re: Help with multiple choise field

swuehl — Mon, 18 Jul 2011 14:48:54 GMT

Hi,

I forgot that you also want that total bar graph.

Please find attached sample qvw.

As I understood, you want a single value selection for project category, right? If not, should it be a OR or AND selection?

Ciao,

Stefan