Formatting key fields?

[mmarchese]

I have a field called [Part Number].  It is 8 digits and may contain leading zeros.  Therefore, I used the Text() function to make sure it is displayed as text.  But this field is a key field that is used to associate multiple tables.  So do I need to use Text() for that field in each table?  What if I did Text() on one table and forgot to do it to another?  Or what if I did Num() on the other table by accident instead?  It seems like an opportunity to provide Qlik with conflicting instructions.

Example:

Table1:
LOAD
   Text([Part Number]) AS [Part Number],
    ...

Table2:
LOAD
   Text([Part Number]) AS [Part Number],
    ...

I also wonder about it for script joins.  For instance, when I tried this, the join did not work correctly:

Table1:
LOAD
   Text([Part Number]) AS [Part Number],
   ...

LEFT JOIN(Table1)
LOAD
   [Part Number],
   ...

What is the best practice here?

And is the advice the same for other types of key fields, such as dates?  Like should I do be doing this?

Table1:
LOAD
   Date([Order Date]) AS [Order Date],
    ...

Table2:
LOAD
   Date([Order Date]) AS [Order Date],
    ...

[johngouws]

Hi,
Two things,
1) I would use the same logic on all the KEY fields.
2) I would identify the KEY field as ????_Key and create [Part Number] as a new field in one of the tables. Then show [Part number] in the chart and not the KEY.
Hope this helps

[anushree1]

The thumb rule is to have the key fields defined in the same format, if not they will not be considered as same and would fail in linking

[mmarchese]

Johngouws:

Interesting.  It seems a bit wasteful memory-wise but safer association-wise.  Is that your reason for doing it?

Do you do this with every key field that the user has an interest in seeing on the interface?

[anushree1]

Well , if the concern is with regard to memory , much better option is to use the autonumber function so that the keyfield gets reduced to a number and you may use it for association making it a key field  and the ones that you need on interface, you may keep it the way you want to.

Hope this is clear. 

[mmarchese]

To partially answer my own question, regarding joining fields that consist of formatted numbers, I experimented and found:

1) Formatting does not hinder joins

2) When there is a formatting conflict, the earliest one in the script wins

Test code and result:

A:
LOAD
    Date(dt, 'WWW YYYY-MM-DD') AS dt,
    val
INLINE [
    dt, val
    40000, 11
];

LEFT JOIN(A)
LOAD
    Date(dt, 'YY-MM-DD') AS dt,
    val2
INLINE [
    dt, val2
    40000, 22
];

C:
LOAD
    Date(dt, 'MM/DD/YYYY') AS dt,
    val3
INLINE [
    dt, val3
    40000, 33
];