8 Replies Latest reply: Aug 28, 2009 1:45 PM by jiir RSS

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

    jiir

      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?

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

          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

           

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

            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.

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

                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?

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

                    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.

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

                        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. Big Smile