10 Replies Latest reply: Sep 15, 2010 11:06 AM by bryankoch RSS

    Compare Variables on One Chart

    bryankoch

      Hi,

       

      I would like to create a chart that compares the averages of different variables. Specifically, I am comparing the length of time for different variables.

      I would like to see a chart similar to

      Days

      | *

      | *
      |
      | *

      |______________ ___

      var1 var2 var3

       

      The variables I want to compare are in raw data form so their columns look similar to

      var1

      1

      5

      6

      3

      6

      2

      etc.

       

       

      And I'm sure this might be pressing my luck, but it would also be really convenient if I could switch from average to min, max, or count.

      Any idea how I can do this?

       

      Thanks!

        • Compare Variables on One Chart
          Neil Miller

          Are all of the variable just lists of days? If so, I would set all the values to a field called Days and then put in a constant Var to identify which variable it is for. Something like:

          LOAD * INLINE [
          Var, Days
          1,1
          1,5
          1,6
          ];
          CONCATENATE LOAD * INLINE [
          Var, Days
          2,3
          2,6
          2,5
          ];
          Etc...

          Then create a chart with Var as the dimension and Avg(Days) as the expression.

          There are multiple ways to handle the different expression options (Avg, Min, Max). I created another load with each of those as a value of a field , Metric. Then for the expression, I used:

          $(=Only(Metric))(Days)
          As long as you have one and only one Metric selected, it will display the proper expression. I've attached a quick sample.

            • Compare Variables on One Chart
              bryankoch

              I can't view the sample because I'm under the personal edition for testing purposes.

               

              However, my concern is with the

              LOAD * INLINE [

              Var, Days

              1,1

              1,5

              1,6

              ];

              code is that I have ~300k lines for 7 different variables. Am I understanding that code correctly in the sense that it's for user defined data, or is there an automated way?

                • Compare Variables on One Chart
                  bryankoch

                  I might add that the variables are the difference between two dates.

                  For example, I have a Date of Service column and a Date of First Payer Acceptance Column and converted this into a integer that's the difference of dates.

                   

                  ie. if DoS = 3/2/2009 and Do1PA = 3/22/2009, then [DoS to Do1PA] = 20

                   

                  As mentioned before, this was done for 7 different variables with about ~300k observations

                    • Compare Variables on One Chart

                      I would go with N Miller. You may call 7 load queries, passing the VarName (Var1,Var2 ...) as a fixed value for each oh them. You would have the first query with the 300k values and with VarName = "Var1", second query, another 300k VarName = Var2, and so on.

                      Qlikview will handle the 7x 300k pretty easily.

                      If the speed is not good and you just want the final value for sum, avg, max, etc (no filters), just calculated this values when loading the data and let them ready for use in a table

                      VarName Sum Avg Max

                      Var1 232 5 10

                      Var2 ..... .. .

                       

                      Cheers

                       

                       

                        • Compare Variables on One Chart
                          bryankoch

                          Thanks for the replies but I don't think I'm understanding correctly. I'm new to SQL and Qlikview so a lot of this seems foreign to me.

                          However, here's what I've come up with and it's not doing what I hoped:

                          LOAD [DoS to DoOS],
                          [DoOS to Do1NA],
                          [Do1NA to Do1O],
                          [Do1O to Do1PA],
                          [DoS to D1RAR],
                          [Do1PA to D1RAR],
                          [DoS to Do1CI]
                          FROM
                          [C:\Users\****];

                          ADD

                          LOAD * INLINE [
                          Vars, Days
                          "DoS to DoOS", [DoS to DoOS]
                          ];
                          CONCATENATE LOAD * INLINE [
                          Vars, Days
                          "DoOS to Do1NA",[DoOS to Do1NA]
                          ];
                          CONCATENATE LOAD * INLINE [
                          Vars, Days
                          "Do1NA to Do1O",[Do1NA to Do1O]
                          ];

                          The bug I get is that there's garbage after the statement. I think because it's an array dimension issue.

                          I guess my question at this point, is how do I "call 7 load queries, passing the VarName (Var1,Var2...) as a fixed value" and assign their name to them?

                           

                           

                           

                           

                            • Compare Variables on One Chart
                              bryankoch

                              Would it be possible to create a for loop to do the trick similar to this? (Again, i don't know how to do this in SQL)

                               

                              let n = NumberOfRows for Var1

                              for i = 1 from 1:n {

                              VarName = "Var"+i;

                              i = i=1;

                              x = i}

                               

                              int Days;

                              Days = concatenate (Days, values_for_var1)

                              Days = concatenate (days,values for var2)

                              ..

                              and so forth

                               

                               

                               

                                • Compare Variables on One Chart
                                  John Witherspoon

                                  You can easily convert from your raw data format to the format NMiller mentioned using a crosstable load.

                                  CROSSTABLE (Var, Days)
                                  LOAD recno() as ID,
                                  ceil(rand()*10) as [DoS to DoOS],
                                  ceil(rand()*11) as [DoOS to Do1NA],
                                  ceil(rand()*12) as [Do1NA to Do1O],
                                  ceil(rand()*13) as [Do1O to Do1PA],
                                  ceil(rand()*14) as [DoS to D1RAR],
                                  ceil(rand()*15) as [Do1PA to D1RAR],
                                  ceil(rand()*16) as [DoS to Do1CI]
                                  AUTOGENERATE 1000
                                  ;

                                  And you can easily handle switching the output between avg, max and count by using an expression group. Write three separate expressions, and then drag them on top of each other to make an expression group.

                                  I know you can't load attachments, but I'll attach a sample file for anyone else that's curious.

                                    • Compare Variables on One Chart
                                      bryankoch

                                      That's the chart setup I need, however I need the full population and not a sample size. I took off AUTOGENERATE 1000. This caused the dataset to not load at all. How can I achieve that setup but with the full population?

                                       

                                      Since the original document is about 300k variables, with the setup I have in mind, I'm expecting close to 2,100,000 rows of data in one table.

                                        • Compare Variables on One Chart
                                          Jonathan Dienst

                                          Bryan

                                          You will need sonething like this:

                                           


                                          SOURCEDATA:
                                          LOAD [DoS to DoOS],
                                          [DoOS to Do1NA],
                                          [Do1NA to Do1O],
                                          [Do1O to Do1PA],
                                          [DoS to D1RAR],
                                          [Do1PA to D1RAR],
                                          [DoS to Do1CI]
                                          FROM
                                          [C:\Users\****];

                                          DATATABLE:
                                          CROSSTABLE (Var, Days)
                                          LOAD recno() as ID,
                                          [DoS to DoOS],
                                          [DoOS to Do1NA],
                                          [Do1NA to Do1O],
                                          [Do1O to Do1PA],
                                          [DoS to D1RAR],
                                          [Do1PA to D1RAR],
                                          [DoS to Do1CI]
                                          RESIDENT SOURCEDATA;
                                          DROP TABLE SOURCEDATA;


                                           

                                          The first part loads the 300k records (I am assuming that you have source file with 300k rows). The second part converts that to a 2.1M row table with the two fields. Then discard the input table (assuming its no longer needed).

                                          I am not certain of the CROSSTABLE syntax, I have just copied JohnW's code.

                                          Hope this helps

                                          Jonathan