Problems in using set analysis on datafield which includes both number and text

[#1]

Hello,

I've had problems with set analysis when I want to compare datafield, which includes numbers and text. So this is basically what I'm doing:

-I've a department filter where is a datafield for example: "100 Administration" .(This is created in script in a "Data Island":

DEPARTMENTCODE &' '& DEPARTMENTNAME as DEPARTMENTINFO

and in other table "Sales" I create similar

DEPARTMENTCODE &' '& DEPARTMENTNAME as DEPARTMENTINFO
(it becomes SALES.DEPARTMENTINFO)

-Then I take all the selections from the filter to a variable:

vDepartmentInfo = IF(GetSelectedCount(DEPARTMENTINFO) <> 0, GetFieldSelections(DEPARTMENTINFO, ',', 1000), '*')

-After this I compare the variable in set analysis clause in this way:

sum( { < SALES.DEPARTMENTINFO = {$(=vDepartmentInfo)} >} AMOUNT )

And it doesn't work..

It works if I change the script to

DEPARTMENTNAME & DEPARTMENTCODE as DEPARTMENTINFO

but this doesn't look nice for the user, because I can't sort the filter by the codes.

What I do wrong or what is wrong in the set analysis? I guess it is something to do with the value beginning by numbers.. I tried also this in script:

text(DEPARTMENTCODE &' '& DEPARTMENTNAME)

but it didn't help.. Any Ideas?

[#2]

Quick and dirty solution

Why don't you use two fields.

1 - DEPARTMENTNAME & DEPARTMENTCODE as DEPARTMENT INFO ( for the set analysis)

2 - DEPARTMENTNAME & ' ' & DEPARTMENTCODE as DISPLAYDEPARTMENT INFO (for the user).

in the same load script

Philippe

[#3]

Thank you from the idea, but the problem in that is that I need the selections from the user to the set analysis (because the department and other this kind of information need to be in a "data island")..

So if user selects from the filter DISPLAYDEPARTMENT INFO, I get only those to set analysis with "GetFieldSelections()"-function (not the values from DEPARTMENT INFO).. And GetPossibleCount gives only the number of the departments, but not the actual info.

And now I noticed that it is possible for the DEPARTMENT NAMES include numbers, - , . , etc. characters.

So is there any possibilities to make the set analysis work for string which includes various kinds of characters?

[#4]

I think Philippe is on the right track there. Separate fields would probably be easier to work with.

I think I found the problem with your Set Analysis. You need quotes around the Set Modifier.

sum( { < SALES.DEPARTMENTINFO = {'$(=vDepartmentInfo)'} >} AMOUNT )

It probably works fine for regular strings, but I believe the space in it is what requires the quotes. Try using:

DEPARTMENTCODE & DEPARTMENTNAME as DEPARTMENTINFO

Use that in your script and I bet your original Set Analysis would work.

[#5]

You can use GetPossibleValues for the DEPARTMENT INFO if the selections are made on the DISPLAYDEPARTMENT INFO field.

Philippe

[#6]

Thank you from these advices but the quote is also problematic because I might have more than one value in the variable.. And it didn't work without spaces either if the DepartmentInfo starts with a number.. (it works if it starts with letters and there aren't spaces, so you are right spaces are one part of the problem)

I made a test application for this, but can I add it somehow to the forum? Well here is the script:

// **********This is made for getting some data into the application
QUALIFY *;
DATA:
LOAD * INLINE [
 DEPARTMENTCODE, DEPARTMENTNAME, AMOUNT
 1, Administration, 500
 2, General, 600
 3, Production, 700
];
 UNQUALIFY *;

// ********** Actual table
QUALIFY *;
Sales:
LOAD DATA.DEPARTMENTCODE as DEPARTMENTCODE, DATA.DEPARTMENTNAME as DEPARTMENTNAME, DATA.AMOUNT as AMOUNT,
 // I would like this to work:
//DATA.DEPARTMENTCODE &' '& DATA.DEPARTMENTNAME as DEPARTMENTINFO
// These don't work either:
DATA.DEPARTMENTCODE & DATA.DEPARTMENTNAME as DEPARTMENTINFO
//DATA.DEPARTMENTNAME &' '& DATA.DEPARTMENTCODE as DEPARTMENTINFO

//Only this works! (if Departmentname doesnt include spaces/numbers in the beginning etc.
 //DATA.DEPARTMENTNAME & DATA.DEPARTMENTCODE as DEPARTMENTINFO

RESIDENT DATA;
UNQUALIFY *;


// *********A seperate Island filter
ISLAND_FILTER:
LOAD DATA.DEPARTMENTCODE as DEPARTMENTCODE, DATA.DEPARTMENTNAME as DEPARTMENTNAME,
 // I would like this to work:
//DATA.DEPARTMENTCODE &' '& DATA.DEPARTMENTNAME as DEPARTMENTINFO

// These don't work either:
DATA.DEPARTMENTCODE & DATA.DEPARTMENTNAME as DEPARTMENTINFO
//DATA.DEPARTMENTNAME &' '& DATA.DEPARTMENTCODE as DEPARTMENTINFO

//Only this works! (if Departmentname doesnt include spaces/numbers in the beginning etc.
//DATA.DEPARTMENTNAME & DATA.DEPARTMENTCODE as DEPARTMENTINFO
RESIDENT DATA; 

And for the charts:

sum( { < Sales.DEPARTMENTINFO = { $(=vDepartment) } >} Sales.AMOUNT)
or

sum

( { < Sales.DEPARTMENTINFO = { '$(=vDepartment)' } >} Sales.AMOUNT)

or



sum

( { < Sales.DEPARTMENTINFO = { "$(=vDepartment)" } >} Sales.AMOUNT)

Philippe, at least my 8.5 or 9.0 applications don't include GetPossibleValues -function, have you used that somewhere?

[#7]

I found a discussion where similar problems have been described: http://community.qlik.com/forums/p/15466/60269.aspx#60269 .

Now I added '_' -marks in the beginning of fields and used also Replace-functions for unlegal signs. It works but it isn't very beautiful.

I feel too that something is wrong with GetFieldSelections -function.

[#8]

My suggestion works for a single selection and the reason it doesn't work for multiple selections is still because of the quotes. If you look at your variable when you make one selection, you see:

'1 Administration'

For multiple:

'1 Administration, 2 General'

You don't have quotes around each value, but instead around the whole thing. That means QlikView is looking for a DEPARTMENTINFO value of that entire string. In order to get it to work, you need single quotes around each DEPARTMENTINFO and a comma between.

Like this:

'1 Administration', '2 General'

In order to get that, you need to modify your variable definition. This is where the issue is, because you would need to put single quotes into your GetFieldSelections separator, but single quotes are needed to define the separator. If you use QlikView's Chr() function, you can get single quotes.

Use this as your variable definition:

=IF(GetSelectedCount(DEPARTMENTINFO) <> 0, GetFieldSelections(DEPARTMENTINFO, Chr(39) & ', ' & Chr(39), 1000), '*')

And then use the Set Analysis I gave you above:

sum( { < SALES.DEPARTMENTINFO = {'$(=vDepartmentInfo)'} >} AMOUNT )

That should work with whatever format you want to put DEPARTMENTINFO in. The key to Set Analysis is to understand what you need and to be able to see what you're getting. The best tip for seeing what you are getting is to put your Set Analysis into a chart, but don't give it a label. Then when the chart is rendered, you will see what your Set Analysis formula looks like.

Also, you can attach a QlikView document to your posts. When composing, look towards the top and there is an Options tab. Click that and you should have a button to add a document to your post. It works with screenshots and other files too.

Whew, sorry, that got a little long.

[#9]

Thanks for the advice! That single quotes tip helps a lot I guess!