topic Re: Text appending 0 while coercing to string in App Development

Text appending 0 while coercing to string

kaustubh — Mon, 18 Mar 2019 20:21:22 GMT

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.

Re: Text appending 0 while coercing to string

Marcos_rv — Mon, 18 Mar 2019 20:31:51 GMT

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!!!

Re: Text appending 0 while coercing to string

kaustubh — Mon, 18 Mar 2019 20:34:34 GMT

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

Re: Text appending 0 while coercing to string

Marcos_rv — Mon, 18 Mar 2019 20:39:13 GMT

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.

Re: Text appending 0 while coercing to string

kaustubh — Mon, 18 Mar 2019 20:42:00 GMT

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.

Re: Text appending 0 while coercing to string

Marcos_rv — Mon, 18 Mar 2019 20:54:34 GMT

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!!!

Re: Text appending 0 while coercing to string

MarcoWedel — Mon, 18 Mar 2019 22:05:18 GMT

Hi,

maybe helpful:

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

regards

Marco

Re: Text appending 0 while coercing to string

kaustubh — Mon, 18 Mar 2019 22:27:17 GMT

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.

Re: Text appending 0 while coercing to string

kaustubh — Mon, 18 Mar 2019 22:28:41 GMT

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.

Re: Text appending 0 while coercing to string

MarcoWedel — Mon, 18 Mar 2019 22:35:33 GMT

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

Re: Text appending 0 while coercing to string

kaustubh — Tue, 19 Mar 2019 13:54:43 GMT

Dear All,

I went through few articles and tried using Text function as per one of the suggestions here during QVD creation. I recreated everything from scratch and now it is working. Thank you here to everyone for your help. It was frustrating.

Take Care.