topic Re: Set Analysis Help in QlikView

Set Analysis Help

Sat, 23 May 2015 08:44:14 GMT

For Example,

I have a table like this:

in the qv, when prod1 is selected, the result is like this below:

However, I wanna the result like this:

Could anybody help me write the expression?

Thanks in advance.

Re: Set Analysis Help

vvvvvvizard — Sat, 23 May 2015 09:12:03 GMT

Attached is the correct display, which is the correct way to show whether hosp a b ,c has a value or null .

Re: Set Analysis Help

Sat, 23 May 2015 10:01:39 GMT

Hi Dear,

Sorry to bother you again.

I have selected Suppress When Value Is Null and Show ALL Values for Hosp in Dimensions.

Bus I still only get one raw for prod1.

Is there anywhere else I need to set for achieving the result of yours?

Re: Set Analysis Help

Anonymous — Sat, 23 May 2015 10:28:06 GMT

How about something like the attached ?

Re: Set Analysis Help

Sat, 23 May 2015 12:02:32 GMT

Hi,

Try like this..

if(IsNull(Value),0,Value) as Value

in scripting part

Thanks

Harsha

Re: Set Analysis Help

Sat, 23 May 2015 13:18:19 GMT

Oh, It's amazing!

How do you make that?

I cannot find any difference in the chart dimension and expression setting.

could you help explain for me?

Re: Set Analysis Help

Anonymous — Sat, 23 May 2015 13:38:04 GMT

Qianyu

Have look at the load script, also pasted in below. In essence :

a) In line load of your test data in table Data

b) Load distinct values of Prod into table Temp

c) Cartesian join of distinct values of Hosp into table Temp

Table Temp now contains all combinations of Prod and Hosp

d) Outer join table Temp onto table Temp

Which fills in the missing Prod and Hosp combinations giving them a null() value for Value

Then once you have generated this table creating the Straight table is dead easy as you have observed.

I am an advocate of doing everything one can in the script so as to make the dashboard objects as easy to create and simple as possible.

Data :

LOAD * INLINE [

Hosp, Prod, Value

a, prod1, 1

a, prod2, 2

a, prod3, 3

b, prod2, 4

b, prod3, 5

c, prod3, 11

c, prod3, 12

];

Temp :

load distinct

Prod

resident Data

;

Outer Join ( Temp )

load distinct

Hosp

resident Data

;

outer join ( Data )

load

resident Temp

;

drop table Temp ;