Identifying longer number as different

[atsushi_saijo]

SAP timestamp is comprised in 'YYYYMMDDhhmmss.0000000'.

Would you advise how we can load these values as different?

Given such test data:

Test: Load * Inline [

Tst,Col

20150115164521.0000167,A

20150115164521.0000168,A

20150115164521.0000169,A

20150115164521.0000170,A

20150115164521.0000171,A

20150115164521.0000172,B

] (delimiter is ',');

QlikView actually take these as same number, resulting in such:

Test file is also attached. I tried *1000000 but seems not working. AutoNumberHash128 neither.

[Background] SAP has automated/pre-programmed actions. It is almost executed in seconds, however; SAP can timestamp up to 0.0000001 (in theory). I would assume BigData analyses requires such as well.

It seems that when we load from text file externally, we can actually import it in the first step. 

But as soon as we do resident load based on the initial load, it "collapses" into integer.

Is there any way we can segregate the numbers?

[flipside · Accepted solution]

Try storing the field split over two, something like ...

Test: Load subfield(Tst,'.',1) as Tst_1, text(subfield(Tst,'.',-1)) as Tst_2, * Inline [
Tst,Col
20150115164521.0000167,A
20150115164521.0000168,A
20150115164521.0000169,A
20150115164521.0000170,A
20150115164521.0000171,A
20150115164521.0000172,B
] (delimiter is ',');

flipside

[Clever_Anjos]

That´s because you´re exceding a QlikView datetime  precision.

Convert them to strings (check flipside approach)

[atsushi_saijo]

Hi, it does not have to be datetime, but can be treated as simple number as well. I just need the serialized number. (This is why AutonumberHash was tried.)

[atsushi_saijo]

Thank you and indeed this is the only way.

*It seems QlikView has upper limitation on large number as 18 digits.

Here is what I did and it works. To sort, I have to sort using 2 keys.

Test: Load TimeStamp#(subfield(Tst,'.',1),'YYYYMMDDhhmmss') as Tst_1,

  Num(Replace(LTRIM(REPLACE(text(subfield(Tst,'.',-1)),'0',' ')), ' ', '0')) as Tst_2,

  RowNo() as Tst_3

Inline [

Tst

20150114083022.0000007

20150114083022.0000008

20150114083022.0000009

];

[atsushi_saijo]

Thanks, and it seems we have upper limit in number handling as 18 digits. So we would also suffer just any numeric values.