Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

kaustubh
Contributor

Text appending 0 while coercing to string

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.

1 Solution

Accepted Solutions
Marcos_rv
Contributor

Re: Text appending 0 while coercing to string

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

10 Replies
Marcos_rv
Contributor

Re: Text appending 0 while coercing to string


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
Contributor

Re: Text appending 0 while coercing to string

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

Marcos_rv
Contributor

Re: Text appending 0 while coercing to string

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
Contributor

Re: Text appending 0 while coercing to string

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
Contributor

Re: Text appending 0 while coercing to string

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

Highlighted

Re: Text appending 0 while coercing to string

kaustubh
Contributor

Re: Text appending 0 while coercing to string

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
Contributor

Re: Text appending 0 while coercing to string

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

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