Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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!

1 Solution

Accepted Solutions
jonathandienst
Partner - Champion III
Partner - Champion III

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
;

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

10 Replies
Not applicable
Author

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.

Not applicable
Author

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?

Not applicable
Author

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

Not applicable
Author

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

Not applicable
Author

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
;

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?

Not applicable
Author

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

johnw
Champion III
Champion III

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.

Not applicable
Author

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.

jonathandienst
Partner - Champion III
Partner - Champion III

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
;

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein