Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
cbaqir
Valued Contributor

Data type mismatch?

I'm not sure how this is possible but to address an issue of data not displaying correctly, I have created a sample QVW and also stripped down my actual application to two tables - a Fact table and a Dimension table. They both join on a field called REQ_ID. In the Fact table, this field is alphanumeric and is aliased to join to the Dimension table. In the Dimension table, it is also alpha-numeric.

The issue in my real QVW file is that for the REQ_ID values that are alphanumeric (as opposed to integers), some of the other field values are not coming through (REQ_NAME = TBD , for example). There are also several other joins in the actual application between the FACT table and other Dimension tables on a field called REQUEST_ID but this was stripped out to identify the issue.

12-15-2015 1-24-46 PM.jpg

When I tried to replicate this in my sample, the TBD names came through just fine. I've even tried loading the real fields as text() but it didn't work. I've been at this for days and cannot figure it out.

Here's a data model view of the stripped down real file (APP_ELD = FACT table):

12-15-2015 1-13-37 PM.jpg

Attached is my sample that I could NOT get to replicate the issue (TBD is what I would want to see):

Note  REQ_ID is displayed as REQUEST_ID

12-15-2015 1-25-45 PM.jpg

I am completely out of ideas and steam.

Help, please!

Tags (3)
1 Solution

Accepted Solutions
cbaqir
Valued Contributor

Re: Data type mismatch?

Turns out that the issue was related to this piece of Load Script Code because my FCAST* rows had a REQ_STATUS of NULL:

where REQ_STATUS not in ('Not Submitted') 

fixed by:

where isnull(REQ_STATUS, 'x') not in ('Not Submitted') 

Thanks for the help, though!

View solution in original post

7 Replies
MVP & Luminary
MVP & Luminary

Re: Data type mismatch?

When you hover over the REQ_ID field in the Dimension table, what is the subset ratio?

-Rob

cbaqir
Valued Contributor

Re: Data type mismatch?

97%

MVP & Luminary
MVP & Luminary

Re: Data type mismatch?

So what that means is that 3% of the REQ_ID values in your fact table don't have a match in the Dim table. So the trick is to identify which values and why. I think you were already on that track but here's a suggestion to clarify the problem.

Load the REQ_ID and extra time in each table, in the DIm table a REQ_ID_DIM, and the fact as REQ_ID_FACT. Then you can do a combination of Select All and Select Excluded in each of the listboxes to see what doesn't match, and what the associate REQ_ID values look like.

-Rob

cbaqir
Valued Contributor

Re: Data type mismatch?

How does this address the fact that I'm not seeing TBD for the REQ_ID's that start with FCAST?

MVP & Luminary
MVP & Luminary

Re: Data type mismatch?

I'm assuming that you are not seeing REQ_NAME values for those keys because there is no FCAST* value in the Dim table, right? There is a mismatch between the key values. My suggestion was just a method to help identify what the key mismatch is. It doesn't correct the problem, but hopefully identifies where the problem is. Am I misunderstanding?

-Rob

cbaqir
Valued Contributor

Re: Data type mismatch?

The values are in both the DIM and FACT tables.

cbaqir
Valued Contributor

Re: Data type mismatch?

Turns out that the issue was related to this piece of Load Script Code because my FCAST* rows had a REQ_STATUS of NULL:

where REQ_STATUS not in ('Not Submitted') 

fixed by:

where isnull(REQ_STATUS, 'x') not in ('Not Submitted') 

Thanks for the help, though!

View solution in original post