Solved: Re: Leading zeros, text() not working - Qlik Community

Report Inappropriate Content · ‎2014-07-15

Hi guys I've some columns with the id: 3755 and 00003755 and they're diferent entities, but qlikview is assuming they are the same,

what I've done was loading as text like this:

See my entire script bellow ->

But qlikview still assumes they're numbers, from what i read the text() function should force qlikview to interpret as text and not number.

I've deleted all my script and attached a qvd with the problem only, if you search for the 3577 you see the id is repeated twice because the first is "3755" and the second is "00003755" and qlikview doesn't interpret as text, but in the load i put the text() function

Report Inappropriate Content · ‎2014-07-15

Ok from your answer I've reviewed the source file and notice that 00003755 was missing so I've replaced by the original one with the 00003577 and 3577, on resume Qlikview is doing the right thing, yesterday stayed up late and maybe i messed up the file and this morning i didn't notice i remember opening the file with excel (wich remove the leading zeros) and saved. The original file have 2 id's the: 00003577 and the 3577 with different values and the qlikview is doing right. My apologies and thanks specially to @Ashfaq.

View solution in original post

ashfaq_haseeb · ‎2014-07-15

Hi,

try below

text(num(Responsible)) as Responsible,

Or

if possible try to share sample data.

Regards

ASHFAQ

alexandros17 · ‎2014-07-15

If the lenght of the field is fixed (for exampleis 10) then:

Load

...

Repeat('0',10-Len(MyField)) & MyField as MyField

...

in this way the field will be formatted as text with leading zero

Let me know

Report Inappropriate Content · ‎2014-07-15

hi,

While loading the data into qlikview

Load id Likre this

Load

text(id) as id,

.....

form....

Report Inappropriate Content · ‎2014-07-15

No the lenght is not fixed and can be varchar or number so i want to read as text to avoid errors on data, i can share my entire script if you want

Report Inappropriate Content · ‎2014-07-15

If you read my question that is what i'am doing

jonathandienst · ‎2014-07-15

Ashfaq is correct:

...

Text(id) As id,

Qlikview is interpreting id as a number and in that case 00003575 = 3575, so that is correct. Forcing it to load as text fixes the problem.

HTH

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

alexandros17 · ‎2014-07-15

Then use this other logic:

when you load that field use Num before:

Load

...

Num(MyField) as MyField

...

In this way you will transform in numbers (the way to transform in string is not easy because strings have different lenght)

sudeepkm · ‎2014-07-15

did you get expected result after using the text function?

ashfaq_haseeb · ‎2014-07-15

Hi,

Can you share few sample records in Excel?

Regards

ASHFAQ