Text appending 0 while coercing to string

[kaustubh]

Dear All,

I have to join two tables based on string field. 'referral' and 'refprov'.

The referral has column 'refer_from' and refprov has 'rfocode'. When I use text(), the string '20' and '020' both turns to '020'.

But in reality they are separate physician code.

Data of refprov:

rfocode   rfoname

20               Robert W

020             Sultan A

Data for referral:

refer_from       referralnum

20                          2842

result expected:

Table 1  View                                                                           Table 2 view

referring Phy.           Code                                                            ref.no.

Robert W                       20                                                               2842

This must be displayed in QlikSense sheet on table.

Can someone please guide me on what might be causing this? Is there any work around or am I missing anything?

Thanks.

[Marcos_rv]

but you have related the 2 tables in the model?

you should have them relaconadas and add the text () in both key fields (field by which the tables are related) and there you will have everything related, remember that if text '20' and 20 are not equal. so I recommend using text in cambas, not number because it breaks the '020' that is not equal to '20'.
regards!!!

[kaustubh]

Yes I have them related. I have used text() on both sides. But no success.

[Marcos_rv]

you have to call the field the same on both sides, for example

Table 1:

load ...

....

text (rfocode) as key

from.... ;

table2:

load ...

....

text (refer_from) as key

from .... ;

There should relate without any problem.

[kaustubh]

This is what exactly I have written. But when the script encounters '020' or '018' and '20' and '18' then it treats both as '020' and '018' which is where the script fails. I am struggling with this.

Kindly help.

[Marcos_rv · Accepted solution]

well you should check in the extraction part, there you should use the sentence text, this is because when qv sees a value '002' it will take it as a number, and when a record falls with the value '02' or ' 2 ', it will leave it as' 002', this is because '002' was the first to be loaded in the table.

what you have to do is move to text after extracting from the database.

for example

table1:
load
id,
text (key_number) as key_number;
sql select id, key_number
from something.table1;

there you would have the values ​​like '002', '02', '2' all as text.

for me there you could work.

if I'm not clear you could pass me the script with some data.

Regards!!!

[MarcoWedel]

Hi,

maybe helpful:

https://community.qlik.com/t5/New-to-QlikView/Subfield-Issue/m-p/1380597/highlight/true#M342770

regards

Marco

[kaustubh]

I added text in extraction layer as well but to no avail.  it still treats 20 and 020 as either both '020' or 20 rather than separate.

[kaustubh]

Dear Marco,

I went through the link but I was unable to relate how that would solve my problem. My very problem is text appending 0 to 20 making it 020. 

Please help.

[MarcoWedel]

can you post a small sample application that demonstrates the issue?