topic Re: Missing data in QlikView

Missing data

Thu, 08 Aug 2013 09:12:34 GMT

Hi Guys,

I am sure this has already been posted,

can anyone point me in the right direction.

i have a data table with missing values for set catergories

Header 1	Header 2	day 1	day2	day 3	day 4
catergory 1	1 week	1	2	3	4
	2 weeks	2	4	6	8
	3 weeks	3	6	8	10
Catergory 2	1 week	1	2	3	4
Catergory 2	3 week	3	6	8	10

Currently i have the above,

i would like qlikview to generate the data to insert 2 weeks into catergory 2 with 0 value so that every catergory has a full set of week ranges 1-3 even if 0

Hope this is clear

Many thanks

Re: Missing data

Anonymous — Thu, 08 Aug 2013 09:23:34 GMT

Do you want to have an extra row for Category 2 Week 2 ?

Try checking the 'Show All Values' checkbox on the Week dimension (on the Dimension properties tab), and making sure the 'Supress zero-values' checkbox (on the Presentation properties tab) is unchecked.

Jonathan

Re: Missing data

Thu, 08 Aug 2013 09:37:53 GMT

Yeah an extra row will be needed,

Qlikview will have to genrate this data as it is not in the data source.

Many thanks

Re: Missing data

Anonymous — Thu, 08 Aug 2013 10:55:43 GMT

Understood, but the dimensions are, namely Category 1 and 2, and Weeks 1, 2 and 3 ?

You should be able to get your Pivot table to include combinations even where datapoints don't exist for each combination.

Jonathan

Re: Missing data

Thu, 08 Aug 2013 11:48:08 GMT

I can in Excel as a pivot, but i cannot seem to manipulate Qlikview to mirror the pivot in excel.

Re: Missing data

Anonymous — Thu, 08 Aug 2013 12:25:44 GMT

No luck with the settings I suggested ?

Trysetting up a table with all combinations then. This can be done by using a cartesian join. You can then join on a dummy value of 0 to force the table to have some values. Something like this:

DummyTable:

LOAD

Re: Missing data

gajapathy74 — Thu, 08 Aug 2013 12:36:30 GMT

hi mikeginvanalyst,

can you share your table(s) structure. meaning how many tables you have or how the data is loaded into QlikView?

Re: Missing data

Thu, 08 Aug 2013 12:51:29 GMT

One table, loaded into qlikview view as an excel sheet.

Re: Missing data

gajapathy74 — Thu, 08 Aug 2013 13:43:50 GMT

see below an alternate method to view the data with missing value shown as 0 (zero).

Re: Missing data

Anonymous — Thu, 08 Aug 2013 14:50:40 GMT

See post above (http://community.qlik.com/message/376992#376992) with example, done with one table containing Category and Week.

Jonathan