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

# Compare Variables on One Chart

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

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

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

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

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

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

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

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

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

• ###### Compare Variables on One Chart

Perfect!

Thanks for all the input from everyone!