autoformat for large numbers ex. credit cards insane

[renovero]

Hello,

I just have to insert to Qlik some large numbers, 24 digits. for example :

1090 4778 5886 0000 0001 0558

I have made an excel, i have paste there the codes, and tried to import file to Qlik.
When it is done, my number is showed as :

1,09E+25

Why? I don't wanna to make any calculations on those numbers, there are card numbers, and has to be only shown, as they were inserted. It gives me crazy, I have checked almost everything which I could found on table options in Qlik, and everything which I could found here on forum.

and still - the number is only shown by those idio**c format.
I have even load by text(XXX), and still.

Please, tell me it is possible to disable auto-format for numbers. Or force shown digit by digit.
Please help I'm out of ideas.

[marcus_sommer · Accepted solution]

Normally the approach of using text(Field) to prevent a numerical interpretation works well. I could imagine that your problem is caused from your Excel because the real fieldvalues might be different to your formatted view of them. Therefore I suggest you make sure that the Excel contained real strings and not a number.

- Marcus

[vvira1316]

Not sure why you are having problem. Please check following. I tried using inline and excel. In both cases it worked. Please include your sample excel to understand/help you further.

NoConcatenate
CardTmp:
//LOAD * Inline
//[Card#
//1090 4778 5886 0000 0001 0558
//];
LOAD Card#
FROM
[Card.xlsx]
(ooxml, embedded labels, table is Sheet1);

NoConcatenate
CardData:
LOAD
Text(Card#) as Card#
Resident CardTmp;

DROP Table CardTmp;

[Vegar · Accepted solution]

Below you see a couple of tricks I use to shake the number format out of a text field with numbers. I hope that at least one of them will help you.

LOAD
Creditcardnumber as as Creditcardnumber_4, 
Text(Creditcardnumber) as Creditcardnumber_3,
Dual(Text(Creditcardnumber),Creditcardnumber) as Creditcardnumber_2,
''&Creditcardnumber&'' as Creditcardnumber_1 //adding empty strings could shake the number format
FROM SOURCE
;

[renovero]

Hello, TY for your time, my friends 🙂
Nevertheless, none of your way-out's have helped me.
@Vegar, I'm glad You gave me those examples, it will be huge help for the future.
@vvira1316, as I said, it didn't work, I "semi-know" how it should be, and how to do it, to load cells from excel to qlikview, but as I was saying - it didn't work in that case

and @marcus_sommer , I think that your advice is the closest. I have double check, and even select all cells and give them "text" format, but still - it did nothing, none of my data changes. so next, I have created an new excel and saw that I have to insert data once again for changes to appear. But still, even if I see my own inserted data on an cell, in right format, when I click on that cell, I can see the invalid one (1,0288562E+25) in function field above the sheet. so the case is in Excel fault.
But besides, I think that, even when I have non-edited columns, and Excel is doing his "magic" on inserted data, the Qlikview should reconfigure it, change it for forced format, or get rid of it, and point his own conditions, because data were loaded and now, they are in possession of Qlik. But it doesn't.

I think, i will try to save an file in txt, csv file, maybe then, the convert will be finally over.

[marcus_sommer]

A workaround to bypass this problem might be to split this card-number within the Excel into two fields and then to concat them in Qlik again.

- Marcus