topic Re: Formatting key fields? in App Development

Formatting key fields?

mmarchese — Mon, 20 Dec 2021 21:32:42 GMT

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], ...

Re: Formatting key fields?

johngouws — Tue, 10 Mar 2020 06:38:41 GMT

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

Re: Formatting key fields?

anushree1 — Tue, 10 Mar 2020 07:18:46 GMT

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

Re: Formatting key fields?

mmarchese — Tue, 10 Mar 2020 12:24:43 GMT

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?

Re: Formatting key fields?

anushree1 — Tue, 10 Mar 2020 12:39:44 GMT

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.

Re: Formatting key fields?

mmarchese — Tue, 12 May 2020 17:51:33 GMT

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 ];