7 Replies Latest reply: Sep 13, 2013 3:34 PM by Andrew Blom RSS

    Assigning a numerical value to text strings in Qlikview

      Hi All,

       

      I'm hoping qlikview is able to do the following so that I dont have to do it in excel first and wondered if anyone here might be able to provide any assistance?

       

      I have some data with a 5 possible answers as shown below

       

      Name          How has your weight changed over the last year?    
      Arnold          Increased A lot

      Jenny          Increased a little

      Max               Not changed

      Heather          Decreased a little

      Ron               Decreased a lot

       

      What I want to do is show these on a guage using by assigning each possible answer a number, so Increased a lot = 2, increased a little = 1, no change = 0 etc into -1 and -2.

       

      Is there any way I can do this In qlikview?

      Ideall I would like to add it as an extra field so can I incorporate into the load script?

       

      Any help would be greatly appreciated.

      Thanks,

      Andrew

        • Re: Assigning a numerical value to text strings in Qlikview
          Michael Solomovich

          Andrew, you can do it using dual() function.  No need for an additional field.  Can be done with mapping or with 4-level "if".

           

          With map:

           

          MyMap:

          MAPPING LOAD * INLINE[

          A,                          B

          Increased A lot,       2  

          Increased a little,      1

          Not changed,          0

          Decreased a little,     -1

          Decreased a lot,          -2

          ];

           

          data:

          LOAD

          dual([How has], applymap('MyMap',[How has])) as [How has],

          ...

           

          The new [How has] is the text and the number at the same time.

           

          Regards,

          Michael

          • Re: Assigning a numerical value to text strings in Qlikview
            Vegar Lie Arntsen

            You can use Dual() values.


            Here is one way of doing it.


            CreateDuals:

            LOAD dual(text,number) as YourFieldName INLINE [

            text, number

            Increased a lot, 2

            increased a little,1

            no change = 0 ];



            Load the rest of your datamodel.


            Drop the tabl CreateDuals.


            /Vegar

              • Re: Re: Assigning a numerical value to text strings in Qlikview
                Michael Solomovich

                vegar.lie.arntsen,

                Technically it is fine, but not good from the data modeling point - you create additional table which is unnecessary.  Dual is better.

                 

                Andrew,

                See example attached.  notice taht I have two lisy boxes for Answer - one is in text fromat, another in numeric, and it is the same field.

                 

                Regards,

                Michael

                  • Re: Re: Re: Assigning a numerical value to text strings in Qlikview
                    Vegar Lie Arntsen

                    I missed the point that my solution only works if you have the numeric values in the transaction table (not the text) and you want to introduce a dual text value. So in this case it will not work, the applymap solution is one way to go.

                     

                    mov

                    I think you misunderstood the solution I presented. Yes I introduce a table in the model, but it is only temporary. I delete it after population my field with transaction data. The applymap-method is much slower and breaks the optimized load of qvds, the predefined dual method is faster and does not break optimized load.

                     

                    Example code:

                     

                    MyTempDualMaker:
                    LOAD dual(A,B) as Answer INLINE [
                    A, B
                    Increased A lot, 2  
                    Increased a little, 1
                    Not changed, 0
                    Decreased a little, -1
                    Decreased a lot, -2];
                    
                    YourDataSource:
                    LOAD * INLINE [
                    Name, Answer  
                    Arnold, 2
                    Jenny, 1
                    Max, 0
                    Heather, -1
                    Vegar, 0
                    Ron, 2
                    ];
                    DROP TABLE MyTempDualMaker;
                    
                    
                • Re: Re: Re: Assigning a numerical value to text strings in Qlikview

                  Hi both,

                   

                  thanks for all your help, but really not getting on very well with this after scouring through everything youve said.

                   

                  I've added this to the load script:

                   

                   

                  MyMap:

                  LOAD dual(A,B) as [UncertaintyComparedToLastYear] INLINE [
                  A, B
                  Increased significantly, 2
                  Increased somewhat, 1
                  Broadly unchanged, 0
                  Declined somewhat, -1
                  Declined significantly, -2
                  ]
                  ;

                   

                  <<<Then I carry on loading my data.... there are several tables>>>

                   

                  Directory; 

                  LOAD

                   

                   

                  [Unique ID],
                  Year (Created) as Year,

                  Name,
                  [Email address],
                  Industry,
                  UncertaintyComparedToLastYear,

                   

                  It just doesnt seem to be working... can you help?

                  the uncertainty field is the one I want to have mapped - you can see the 5 possilbe options from the MyMap

                   

                  Kind regards and thanks for your help,

                  Andrew