topic Re: Dimensions with No Facts in QlikView

Dimensions with No Facts

Tue, 16 Jun 2015 15:20:20 GMT

Hi All,

I am new to Qlikview and currently getting hold of the usage of the tool. I have a requirement where in I need to create a Straight table with Dimension and Facts. The Drawback is for certain dimensions there is no data in the fact table, Consider the example as attached. I have a accounts table with header and sub header but these records will not have corresponding data in Fact table. But i need header and sub header to be part of straight table. for this i created a resident table with max and min account_key for a particular account and used a set analysis expression Sum({1<account_key={">=$(=MinVal)<=$(=max(MaxVal))"}>}1) to include all the records for a particular account selection. The requirement here is i do not want any number to be displayed in the header and sub header line, currently it is displayed as zero, i need a null or nothing to be displayed. I cannot generate the facts inline or insert them as i have thousands of sub header under a header and nearly 20-30 million rows needs to generated. Please suggest a way to avoid data generation and handling the same in Qlik.

I am attaching the sample data and qvw.

Note I am using a trial version, any suggestion please paste it in the forum instead of modifying and attaching the qvw, i will not be able to open it.

Re: Dimensions with No Facts

stigchel — Tue, 16 Jun 2015 15:51:58 GMT

You can use something like this for your Amount expression

If(Account_line>0, Sum(Amount))

Re: Dimensions with No Facts

Tue, 16 Jun 2015 16:04:59 GMT

I am sorry, i forgot to mention, i do not have facts for certain 'D' accounts too in the facts, i need those records to be displayed as Zero in the report. I have attached the modified test data and qvw. report should be as below

Account Line	Acc_desc	Amount
	Head1
	SubHead1
3	rec1	300
4	rec2	300
5	rec3	300
6	rec4	0
7	rec5	0

Re: Dimensions with No Facts

stigchel — Tue, 16 Jun 2015 17:04:20 GMT

You can use the only function with set analysis to disregard any selection for the Account line check

=if(Only({1} Account_line)>0,Sum(Amount))

Re: Dimensions with No Facts

Tue, 16 Jun 2015 19:49:28 GMT

The Account Line in my application is a text field with values as 0001,0002 etc, also if include the same in my application, where in the Account line may span across different headers, i am getting a multiple values for same line from different headers too

Re: Dimensions with No Facts

stigchel — Tue, 16 Jun 2015 20:12:54 GMT

This will become difficult if the examples you post don't match your real life scenario. For text instead of numbers there is a num function or you can check the length of the string instead using the len function. That does not change the basic logic I suggested for you, check whether there is some condition whether to do the calculation or not, because the Sum function will not result in null.

Can you post your real application instead of a sample? If you're worried about data sensitivity, please have a look at this

Preparing examples for Upload - Reduction and Data Scrambling

Re: Dimensions with No Facts

Tue, 16 Jun 2015 20:12:59 GMT

I am used =if(Only({1} IsNull(Account_line))=0,Sum(Amount)) expression, also when the suppress zero rows is checked, the multiple values issue is fixed, thanks for your direction. Also thank you for sharing the info on scramble option

Re: Dimensions with No Facts

stigchel — Tue, 16 Jun 2015 20:15:10 GMT

Ok, great glad to be of help