topic Re: Showing missing values and year in chart in QlikView

Showing missing values and year in chart

Wed, 22 Jan 2014 19:43:34 GMT

Hi,

I would like to create a chart which will display the missing values

ex. Assuming below is my Year table, category table and order table

[YR]:

load * inline

[YR

2004

2005

2006];

[CAT]:

load * inline

[CAT

D];

[ORD]:

LOAD * inline

[CAT,YR,AMT

A,2005,100

A,2006,200

A,2005,100

C,2006,200];

Here is the chart I would like to create . Show all categories A, B & C . Also show all years as well. Ex. It shoul list B and all the years with 0 as amount.

Any idea ?

Thanks,

Re: Showing missing values and year in chart

Anonymous — Wed, 22 Jan 2014 19:57:23 GMT

Aji

Does the attached give you food for thought ?

Best Regards, Bill

Re: Showing missing values and year in chart

Wed, 22 Jan 2014 20:08:46 GMT

Bill,

Thanks for the quick reply!

Is there any way other than creating dummy (0) values? I have few dimensions , so in that scenario I have to loop thru all the dimensions.

Thanks,

Re: Showing missing values and year in chart

Wed, 22 Jan 2014 20:25:19 GMT

Hey Aji,

The trick is to unckeck 'Suppress Zero Values' in Presentation tab. As long as there are valid dimensions(in this case Year) even if the expression results in zero or null() still it would be shown in the table. This is what Bill Markham did.

Hope it helps.

Thanks

Re: Showing missing values and year in chart

Anonymous — Wed, 22 Jan 2014 20:39:02 GMT

AJ, AP,

Bill Markham did a little more than that... He modified data model by joining all table. But adding AMT=0 seems unnecessary. Try to remove script lines "0 as AMT" - the result is the same.

Regards,

Michael

Re: Showing missing values and year in chart

Wed, 22 Jan 2014 20:39:59 GMT

Ajay,

I mean without joining the table is it possible ?

--------------------------------Single table w/Outer Join ----------------------

[YR]:

load * inline

[YR

2004

2005

2006];

outer join (YR)

load * inline

[CAT

D];

outer join (YR)

LOAD * inline

[CAT,YR,AMT

A,2005,100

A,2006,200

A,2005,100

C,2006,200];

-------------------------Individual tables --------------------------------

[YR]:

load * inline

[YR

2004

2005

2006];

[CAT]:

load * inline

[CAT

D];

[ORD]:

LOAD * inline

[CAT,YR,AMT

A,2005,100

A,2006,200

A,2005,100

C,2006,200];

Re: Showing missing values and year in chart

Wed, 22 Jan 2014 20:41:43 GMT

Thanks Michael!

I have a very complex data structure so I was wondering whether its possible to do with out joining.

Thanks,

Aji

Re: Showing missing values and year in chart

Wed, 22 Jan 2014 20:55:36 GMT

I think without Joining its not possible(because there is no way of telling QV that A,B,C and D should all have all three years) . You dont have to join the entire table to Ord. You might be having 20 fields in YR and CAT but just join Year and CAT like in the example.

Re: Re: Showing missing values and year in chart

Anonymous — Wed, 22 Jan 2014 20:55:53 GMT

Aji,

It is possible without joining, but it is more complex. See attached, for example. You'll not like it...

Regards,

Michael

Re: Showing missing values and year in chart

Thu, 23 Jan 2014 11:57:08 GMT

Hi,

My proposal in attached (after several tries...): since the number of years and categories should be small, you could load them in new independant tables without joining to ORD (by naming fields differently, YR2 and CAT2 for instance, or use Qualify/Unqualify). Your pivot chart could then use YR2 and CAT2 as dimensions, and as expression Sum(If(CAT=CAT2 and YR=YR2, AMT))

Hth