Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

hammermill21
Contributor II

Data Containing a Zero Clears out Other Data

Hello!

I have an excel sheet that has data as "0" so when I pull it into Qlik it clears out other data attached to it. So my excel sheet has the Facility name as UHT and within that row, it has the column 14 as 0 which is correct. Now on QLik it pulls the data but shows me the Facility as "-". Now if I change the number on my excel sheet it will show me the facility name on Qlik. What could cause that? I have it allowing Null Values.

Excel:

Capture22.PNG

Qlik:

Qlik.PNG

If anyone has any ideas why this his happening I would greatly appreciate it.

Thank you!

1 Solution

Accepted Solutions
dwforest
Valued Contributor

Re: Data Containing a Zero Clears out Other Data

The data for that row isn't 0, it's null and Excel Display format is showing 0. Qlik doesn't have that feature.

In your load script replace nulls with 0.

If(isnull(Score),0,Score) as Score

5 Replies
dwforest
Valued Contributor

Re: Data Containing a Zero Clears out Other Data

The data for that row isn't 0, it's null and Excel Display format is showing 0. Qlik doesn't have that feature.

In your load script replace nulls with 0.

If(isnull(Score),0,Score) as Score

sureshqv
Esteemed Contributor III

Re: Data Containing a Zero Clears out Other Data

did you  add all dimensions in table which having in excel ?

hammermill21
Contributor II

Re: Data Containing a Zero Clears out Other Data

That makes sense! Thank you.

I added it to my load script but I'm getting an error:

Capture33.PNG

JustinDallas
Valued Contributor

Re: Data Containing a Zero Clears out Other Data

The '*' means you are loading the Score field (under the asterisks) and then you are loading it again with your IsNull statement.

You have essentially written

LOAD Facility, Score, If(IsNull(Score),0,Score) AS Score...

You will either have to rename your field that is the result of your null test, or replace the asterisks with the fields you want excluding the Score field.

hammermill21
Contributor II

Re: Data Containing a Zero Clears out Other Data

I understand now! and was able to get it working.

Thank you!!!

Community Browser