Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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!!!
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!!!
Yes I have them related. I have used text() on both sides. But no success.
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.
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.
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!!!
Hi,
maybe helpful:
https://community.qlik.com/t5/New-to-QlikView/Subfield-Issue/m-p/1380597/highlight/true#M342770
regards
Marco
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.
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.