12 Replies Latest reply: Jan 26, 2010 1:34 PM by John Witherspoon RSS

    Overide Value

      All,

      A slightly unusual requirement.

      We have some data as follows;

      SystemID, OverRideID, SCORE

      1, , 100

      2, , 90

      3, , 95

      4, 1, 20

      We would like to create a bar chart that shows SystemID and Score.

      SystemID 1 should have a score value of 100

      SystemID 2 should have a score value of 90

      SystemID 3 should have a score value of 95

      SystemID 4 should have a score value of 100 (Because we want it to inherit the score from SystemID 1)

      We tried code such as the following but is doesn't work and the bar chart still displays the 20 value.

      if(isNum(OverRideID), OverRideID, (if(isNull(OverRideID),SystemID))) as ID;

      Any suggestions?

      P.S. We don't want to override SystemID 4 in all cases. It will only be required for this chart.

      Tom

        • Overide Value

          Correction

          That last code example should read;

          if(isNum(OverRideID), OverRideID, (if(isNull(OverRideID),SystemID))) as SystemID;

          • Overide Value

            Can you change your table structure to something like this:

            SystemID, SCORE, OverrideScore

             

            1, 100,

            2, 90,

            3, 95,

            4, 20, 100

            Then your expression would be something like this
            =If(IsNull(OverrideScore), SCORE, OverrideScore)


            • Overide Value

              YOu could use the following expression

              if(isNum(OverrideID),above(Sum(Score),SystemID-OverrideID),Sum(Score))

              I have attached an example file using your example. There is a limitation to using this. The systemIDs will have to be sorted in ascending order and the overideId has to be less the systemid. If these conditions are met in your data model, then it will work.

              PS. By mistake, I pressed the suggested answer to your question.

              Do let me know if this works for you.

              Nimish

                • Overide Value
                  John Witherspoon

                  You can left join the table onto itself to establish an OverRideScore, then do it one more time to establish a FinalScore. Report the FinalScore. Drop the other scores if you don't need them, or leave them if you need the raw information.

                  LEFT JOIN (Data)
                  LOAD SystemID as OverRideID
                  ,SCORE as OverRideScore
                  RESIDENT Data
                  ;
                  LEFT JOIN (Data)
                  LOAD SystemID
                  ,alt(OverRideScore,SCORE) as FinalScore
                  RESIDENT Data
                  ;

                  A second approach is to use a combination of fieldvalue() and fieldindex() to look up the correct score in the chart. Technique courtesy of Peter Rieper in a post yesterday.

                  if(OverRideID,fieldvalue('SCORE',fieldindex('SystemID',"OverRideID")),SCORE)

                  Both techniques are demonstrated on the example data in the attached file.

                    • Overide Value

                      Hi John,

                      This is really good. Is this expensive (especially over large data sets?). Also, are the double quotes required for the fieldvalue pararmeter in the fieldindex?

                      Nimish

                        • Overide Value
                          silky.agarwal

                          You can simply do it with the Hierarchy Load.

                          Check for it in the Qlik View Help.

                          • Overide Value
                            John Witherspoon

                             


                            Nimish Shah wrote:Is this expensive (especially over large data sets?). Also, are the double quotes required for the fieldvalue pararmeter in the fieldindex?


                            Honestly, I'm not sure how expensive those options are. Two left joins in a row will take some time on the load, and the bigger the table, the worse it would be. But then the chart itself is just reporting existing fields, so that part should be just fine. And I haven't done any experimentation on fieldvalue() and fieldindex() speed. My GUESS is that they'll be very fast. My GUESS is that QlikView stores the fieldindex rather than recomputing it on the fly, in which case the chart expression should be very quick, and shouldn't degrade much on large data sets.

                            The single and double quotes should be as I showed them. From the help text:

                            "FieldValue( fieldname , n )
                            Returns the field value found in position n of the field fieldname (by load order). fieldname must be given as a string value, e.g. the field name must be enclosed by single quotes. The first field value is returned for n=1. If n is larger than the number of field values, NULL is returned.

                            FieldIndex( fieldname , value )
                            Returns the position of the field value value found in the field fieldname (by load order). If value cannot be found among the field values, 0 is returned. fieldname must be given as a string value, e.g. the field name must be enclosed by single quotes."

                              • Overide Value

                                Hi John,

                                Thanks for the response. I understand from help that the quotes would be required. But my understanding was that this will be true if literal is passed. In this case, you are passing a FieldName. I tried your sample and removed the quotes around the OverRideID in the fieldindex function. It still works.

                                Sorry but just trying to understand the QV syntax here.

                                Nimish

                                  • Overide Value
                                    John Witherspoon

                                    Ah, sorry for my confusion. If your field name doesn't have any white space or funny characters, you can get away with not putting it in double quotes. If it DOES have white space or funny characters, you must put the field name in either double quotes or in brackets []. I don't always follow my own standards when posting examples, but in my real applications, I try to always put field names in double quotes for consistency, whether they have white space in them or not.

                                      • Overide Value

                                        Thanks all for your replies.

                                         

                                        I was able to resolve this using a reply suggested by Ajay143 on another thread. There a mapping load table was recomended as the solution;

                                         

                                        MAP1:
                                        Mapping Load SystemID,
                                        Score
                                        FROM dbo.....

                                        RESULT1:
                                        LOAD SystemID,
                                        OverrideID,

                                        Score,

                                        If(ISNULL(SystemID)=-1,Score,ApplyMap('MAP1',OverrideID,Score)) as InheritedScore

                                        FROM dbo....

                                         

                                         

                                        • Overide Value

                                          Hi John,

                                          Just noticed that the fieldvalue function works a bit strangely (or the help file is not very clear about this). The n represents only distinct values and not actual values. So if you have field values which are repeated, one will land up in a problem. I just tried this even with example by giving same values to scrores in more than one record.

                                          Have you noticed this?

                                          Nimish

                                            • Overide Value
                                              John Witherspoon

                                              I've actually never used them in real applications, so no, I hadn't noticed. But yeah, I'm seeing the same behavior as you, which breaks that solution when values are duplicated. At least we still have the script solution, and there may be chart solutions as well.