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: 
ttollin11
Contributor III
Contributor III

Tables: Dimensions and Measures

I'm running into a consistent problem when trying to create table objects that I think is caused by a misunderstanding on my part about how tables use dimensions and measures.

If I have a table that should display all records from the data model respective to the user's filters, my understanding of the best case scenario is to have a PK as a dimension and place the rest of the columns in as ="" measures. However, my current data set does not have a PK, it is just a collection of rows and columns. My first thought was to simply add every field as a dimension, but this resulted in the columns displaying mostly blank records except for the fields that have unused data points. This throws my count expressions off.

My second thought was to try to parcel out what I could use to group data, but I am consistently coming up with issues where data is appearing as null where it shouldn't.

Is there documentation on this, or could someone help me with an explanation? This seems like it should be simple, but I'm spending more time trying to deconflict tables like this than I should be.

1 Solution

Accepted Solutions
marcus_sommer

In this case you need to use the exists() with two parameters like above mentioned maybe with:

...

WHERE EXISTS(Emp_Num, Emp_ID);

...

But if possible it's better to use exists() only with one parameter because it's easier and more performant. If you want to rename this field you could do it directly. I mean:

Sales: 

Load 

    Sale_ID, 

    Prod_ID, 

    Cust_ID, 

    Emp_ID 

From tableX; 

Employee: 

Load 

    Emp_ID, 

    Emp_F_Name, 

    Emp_L_Name 

From tableY 

WHERE EXISTS(Emp_ID); 

rename fields Emp_ID to Emp_Num;

- Marcus

View solution in original post

10 Replies
Lisa_P
Employee
Employee

Hi Tyler,

You should be able to create a table with only dimensions to see all your raw data.

The data will be related at row level in each table that is loaded.

When you are counting you can count Distinct values and also put conditions in to not count nulls.

The underlying data model is very important to ensuring that you get the right answer to your questions.

Regards,

Lisa

ttollin11
Contributor III
Contributor III
Author

Thanks for the reply, Lisa.

What I've found is that when I add a field as a dimension from a dimension table, many times I have records created to display items from that dim table that perhaps are not being used in other records. This doesn't fit my needs, but maybe there is a way around this?

Lisa_P
Employee
Employee

Can you give an example please ?

ttollin11
Contributor III
Contributor III
Author

Sure.

In this instance I have a collection of data that contains a single field for an employee's code which uniquely identifies them. Their name, location, or manager is not in this table. To get this information, I bring in an employee table with all employees, their location, manager, and some other things. The relationship between the code from table A and the code from table B is correct, however there are other employees with codes that do not appear in table A. If I bring the employee code field in as a dimension, all possible codes appear on my front-end, whether they appear in table A or not. This bloats my record count with records which are mostly empty except for emp code, emp name, emp location, and emp manager, for instance.

Does this help?

marcus_sommer

Just filter the second load to the available values from the first one, maybe in this way:

facts:

load EmployeeID, ... from tableA;

dim:

load EmployeeID, ... from tableB where exists(EmployeeID);

- Marcus

ttollin11
Contributor III
Contributor III
Author

Thanks Marcus, I like this option.

Is it possible do a where with alias fields? One of my tables has the employee ID as another name. I've also tried

Where

     Exists("empID") or

     Exists("otherEmpID");

marcus_sommer

I'm not absolutely sure what you mean with alias fields but exists() has also an optional second parameter and a statement could look like:

Where Exists("empID", "otherEmpID");

or maybe with an expression:

Where Exists("empID", left(AnyField, 5));

By loading from qvd's or resident-tables and larger datasets (in the above mentioned case it's not related to the fact- else the dim-table which is probably not very large) it's useful to prepare the data in a way that the exists() worked with a single-parameter because it keeps an load optimized.

- Marcus

ttollin11
Contributor III
Contributor III
Author

So if I have...

Sales:

Load

    Sale_ID,

    Prod_ID,

    Cust_ID,

    Emp_ID as Emp_Num

From tableX;

and

Employee:

Load

    Emp_ID as Emp_Num,

    Emp_F_Name,

    Emp_L_Name

From tableY

WHERE EXISTS(Emp_Num);

...this doesn't appear to work, but perhaps I'm doing something incorrectly. In this instance, I can't really get around using an AS.

marcus_sommer

In this case you need to use the exists() with two parameters like above mentioned maybe with:

...

WHERE EXISTS(Emp_Num, Emp_ID);

...

But if possible it's better to use exists() only with one parameter because it's easier and more performant. If you want to rename this field you could do it directly. I mean:

Sales: 

Load 

    Sale_ID, 

    Prod_ID, 

    Cust_ID, 

    Emp_ID 

From tableX; 

Employee: 

Load 

    Emp_ID, 

    Emp_F_Name, 

    Emp_L_Name 

From tableY 

WHERE EXISTS(Emp_ID); 

rename fields Emp_ID to Emp_Num;

- Marcus