Numbers concatenated by a colon mystery

[slavomir_garaj]

Hi, I was wondering when a qlik load changed my composite IDs created as two numbers concatenated by a colon character. See example... Can someone explain what's going on there? Is it feature or bug? Thx.

[tab]:
LOAD * INLINE [
seq, id
1,'1:132550'
2,'1949:15670'
3,'1949:1567'
4,'1949:15670'
5,'1:132550'
];

LET NumRows=NoOfRows('tab');
FOR i=1 to $(NumRows)
  LET vSeq=Peek('seq',$(i)-1);
  LET vId=Peek('id',$(i)-1);

  trace $(i). row: seq=$(vSeq), id=$(vId);
NEXT;

Output: Lines fetched: 5
1. row: seq=1, id=1:132550
2. row: seq=2, id=1:132550    // expecting value 1949:15670
3. row: seq=3, id=1949:1567
4. row: seq=4, id=1:132550    // expecting value 1949:15670
5. row: seq=5, id=1:132550

[swuehl]

Have a look at your tab table in the table view. Your loop with the peek() functions is correctly reporting your record values.

But why did the values changed?

You will notice that the id values are in fact dual values, so Qlik automatically interpreted your values as numbers, and it seems the first two values to the same number. Only one text value is stored with a number, that's why you got your results.

Try

[tab]:

LOAD Text(id) as id INLINE [

seq, id

1,'1:132550'

2,'1949:15670'

3,'1949:1567'

4,'1949:15670'

5,'1:132550'

];

[swuehl]

And if you ask why it was interpreted as number, well, that might depend on your system settings and default formats for e.g. time representation.

[swuehl]

For example, line 1 could be read as 1 hour, 132550 minutes = 132610 minutes.

Line 2 as 1949 hours, 15670 minutes = 132610 minutes.

[swuehl]

Related:

Automatic Number Interpretation

Data Types in QlikView