topic Join two tables on text field in QlikView

Join two tables on text field

Fri, 10 Feb 2012 18:18:01 GMT

I am trying to join/associate two tables on a field that contains text values that look like numbers.

For example, Table A might have the following values in the joining/key field:

'128'

'128.'

'128.0'

And Table B has a value in the joining/key field of

'128'

When I join Tables A and B, I want the value from Table B to only join to the 1st value listed in Table A above. Qlikview seems to be treating the three values in Table A listed above as the same value (numerically = 128). Any ideas on how to make sure QlikView treats those three values as distinct text values?

Join two tables on text field

swuehl — Fri, 10 Feb 2012 18:26:20 GMT

Try using the text() function to force a textual interpretation when loading your data.

Join two tables on text field

Fri, 10 Feb 2012 18:47:03 GMT

Thanks, that was an easy fix! I ended up having to do some manipulation to the data in Table B because it had values like '38' that needed to be '038' (always 3 digits). Do you know if there is a way to do that in the script while loading?

Join two tables on text field

hic — Fri, 10 Feb 2012 20:20:18 GMT

If you always want to format the number with three digits, then use "num(Field, '000') as Field"