Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 | |
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
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
Yeah an extra row will be needed,
Qlikview will have to genrate this data as it is not in the data source.
Many thanks
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
I can in Excel as a pivot, but i cannot seem to manipulate Qlikview to mirror the pivot in excel.
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
hi mikeginvanalyst,
can you share your table(s) structure. meaning how many tables you have or how the data is loaded into QlikView?
One table, loaded into qlikview view as an excel sheet.
see below an alternate method to view the data with missing value shown as 0 (zero).
See post above (http://community.qlik.com/message/376992#376992) with example, done with one table containing Category and Week.
Jonathan