topic Re: Strange behavior Inline load in QlikView

Strange behavior Inline load

Thu, 28 Nov 2013 16:16:11 GMT

Can someone explain this?

LOAD * INLINE
[Name, Value
Name1, "0000"
Name2, "00000000000000"
];

Results in:

And

LOAD * INLINE
[Name, Value
Name1, "0000"
Name2, "0000000000000000"

];

Results in:

Is there a reason for this behavior?

Re: Strange behavior Inline load

Gysbert_Wassenaar — Thu, 28 Nov 2013 16:30:56 GMT

Numbers in qlikview can have only 14 digits. Any string with more digits will be considered text. So in your first example both strings will be evaluated as numbers. But in the second example the second string cannot be interpreted as a number so it ends up as a text value.

Re: Strange behavior Inline load

Thu, 28 Nov 2013 19:30:14 GMT

Use the Num function to keep trailing zeros.

Num(Field , '##########0')

Re: Strange behavior Inline load

Fri, 29 Nov 2013 08:22:03 GMT

Thans for your reply, Gysbert,

If qv interprets the string as a number, why doesn't it display a '0' instead of '0000' in the first example?

I need the input to be strings, so I tried:

let Value = Text('0000');
TEST:
LOAD * INLINE
[Key, Name,Value
1, Name1, $(Value)
];

let Value = Text('000000000 ');
LOAD * INLINE
[Key,Name, Value
2,Name1, $(Value)
];

Same result however:

Re: Strange behavior Inline load

Fri, 29 Nov 2013 08:24:12 GMT

Thanks for your reply dathu.qv,

Could you give an example? I wasn't able to load the values in the first example using this function.

Re: Strange behavior Inline load

swuehl — Fri, 29 Nov 2013 08:47:15 GMT

Because QV also tries to keep the format as seen for the first occurrence. Add another line Name0, 0 as first line to the first inline loaf of your initial post to see what I mean. Use the num() function to explicitely define the text representation of your value (and if you don't already know, search for dual value to read more about this).

Re: Strange behavior Inline load

Fri, 29 Nov 2013 09:27:48 GMT

Thank you swuehl,

Ok, I understand what you mean.

In my case, the values I want to load are strings. So '0000' and '000000' aren't the same and aren't numbers.

(codes for categories in my case, as '0' or '00' could be). The dual function would be helpful, but then I would have to define all categories in advance, and I don't know how many and which to expect.

I'm looking for a way to force qv to load the values as strings.

Re: Strange behavior Inline load

swuehl — Fri, 29 Nov 2013 09:31:39 GMT

Use the text() function

LOAD Name, text(Value) as Value INLINE
[Name, Value
Name1, "0000"
Name2, "00000000000000"
];

Re: Strange behavior Inline load

Fri, 29 Nov 2013 09:42:23 GMT

Hi swuehl,

I already tried that, with no success:

let Value = Text('0000');
TEST:
LOAD * INLINE
[Key, Name,Value
1, Name1, $(Value)
];

let Value = Text('000000000 ');
LOAD * INLINE
[Key,Name, Value
2,Name1, $(Value)
];

Same result however:

Re: Strange behavior Inline load

swuehl — Tue, 03 Dec 2013 17:05:16 GMT

That's not what I've suggested (using text() function in your load). Why are you using variables here?

But anyway, here your go:

let Value = Text('0000');

TEST:

LOAD Key, Name, text(Value) as Value INLINE

[Key, Name,Value

1, Name1, $(Value)

];

let Value = Text('000000000 ');

LOAD Key, Name, text(Value) as Value INLINE

[Key,Name, Value

2,Name1, $(Value)

];

Re: Strange behavior Inline load

Wed, 04 Dec 2013 08:16:13 GMT

Hi swuehl,

Thanks for your reply.

Your solution works. I'm converting data from a legacy system with poor data definitions. Thats why I have to use variables.

Re: Strange behavior Inline load

Wed, 04 Dec 2013 15:16:08 GMT

Hi, Please ignore my last post. Please find the below piece of sample code:

LOAD ID, NAME, NUM(VALUE ,'000000') AS VALUE INLINE [

ID, NAME, VALUE

1, N1, 0

2, N2, 10

3, N3, 101];