Funny problems

[williamyuen96]

I recently created a new app and I stumbled upon a few funny problems.

Below is the problem I found in my app

Problem 1Problem 2

[treysmithdev]

Are you loading from an Excel file? It looks like Problem 1 is loading in the '-' as a string, which Excel sometimes displays as a 0.  

You can tackle this two ways:

// Option 1
// Set Qlik to recognize symbol as NULL
Set NullInterpret = '-';

Sales:
Load
    CategoryID,
    ProductID,
    Value
From
    [.....];

Set NullInterpret=;

// Option 2
// Replace value in load (slower)
Sales:
Load
    CategoryID,
    ProductID,
    Num#(Replace(Value,'-','0')) as Value
From
    [.....];

Problem 2:

This is probably due to an issue on your WeekDay variable:

App Default is Section "Main" line 17:

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

[stevegpage1]

Another possible issue with Excel is an inconsistency in how data is entered/loaded. Occasionally the cell will have a numerical value, which may include a '0'. Other times, the cell is left blank. When it is blank, Qlik will default to null in that case. So an alternative solution can look like:

Sales:

Load

Date,

If(len("Revenue") = 0, '0', "Revenue") as Revenue,

If(len("Profit") = 0, '0', "Profit") as Profit,

.... 

From [.....]

For your second problem, can you post the script you used? 

[williamyuen96]

Thanks for the problem but I have 1 more question..for problem 2

If I do not have permission to enter data load editor, what is the best way to solve that?

[stevegpage1]

If you don't have access to the data load editor you have two options. First, ask the data architect to fix the problem! Assuming that’s not possible, then you have to make the change in the dimension within the visualization. It would look like this:

IF(WeekDay = ‘Waa',  'Sun', 

    IF(WeekDay = ‘abb’,  ‘Mon’, 

        IF(WeekDay = ‘ggg’,  ‘Tue’,

              etc .... )))))))

with the values you are getting. It’s a brute force method, but will translate the values to the WeekDay values you would normally expect.

Ultimately, though, you are going to need a modification to the data load editor, since I would expect you will find other issues if you are getting those kinds of values.