Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

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
jonbrough
Valued Contributor

Re: Missing data

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

Re: Missing data

Yeah an extra row will be needed,

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

Many thanks

jonbrough
Valued Contributor

Re: Missing data

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

Re: Missing data

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

jonbrough
Valued Contributor

Re: Missing data

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
Contributor II

Re: Missing data

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

Re: Missing data

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

 

gajapathy74
Contributor II

Re: Missing data

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

missing data.PNG

jonbrough
Valued Contributor

Re: Missing data

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

Jonathan

Community Browser