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

Missing data

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 1Header 2day 1 day2 day 3day 4

catergory 1
1 week1234
2 weeks2468
3 weeks36810
Catergory 21 week1234
3 week36810

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

9 Replies
Anonymous
Not applicable
Author

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

Not applicable
Author

Yeah an extra row will be needed,

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

Many thanks

Anonymous
Not applicable
Author

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

Not applicable
Author

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

Anonymous
Not applicable
Author

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

      Category

FROM .....

LEFT JOIN (DummyTable)

LOAD

     Week

FROM .....

LEFT JOIN (DummyTable)

LOAD

    Category,

    Week,

    0 AS DummyField

RESIDENT DummyTable;

You'll need to make sure that this DummyTable is linked to the table with the Category and the Week fields. If they're in seperate tables then you'd need to use a different datamodel, such as through a LinkTable.

You can then add the DummyField as an expression into your pivot table, either as a seperate expression or added to the existing expression.

See attached.

Jonathan

gajapathy74
Creator II
Creator II

hi mikeginvanalyst,

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

Not applicable
Author

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

 

gajapathy74
Creator II
Creator II

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

missing data.PNG

Anonymous
Not applicable
Author

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

Jonathan