topic Re: Identifying longer number as different in QlikView

Identifying longer number as different

atsushi_saijo — Thu, 15 Jan 2015 15:02:20 GMT

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?

Re: Identifying longer number as different

flipside — Thu, 15 Jan 2015 15:16:39 GMT

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

Re: Identifying longer number as different

Clever_Anjos — Thu, 15 Jan 2015 15:25:00 GMT

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

Convert them to strings (check flipside approach)

Re: Identifying longer number as different

atsushi_saijo — Thu, 15 Jan 2015 15:39:28 GMT

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.)

Re: Identifying longer number as different

atsushi_saijo — Thu, 15 Jan 2015 17:00:08 GMT

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

];

Re: Identifying longer number as different

atsushi_saijo — Thu, 15 Jan 2015 17:00:58 GMT

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