Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
New Contributor III

Joining tables based on an altered field

Further to this thread last week in which people were kind enough to point me at a "data scrubbing" function:


I now have a further question...can one use this "Scrubbed" field to join to another table?

So here is what I tried to do.  This contact table has phone numbers that have no particular format - so I need to remove all non numerics and THEN use that cleaned up number to join to another table of phone calls, where those numbers are recorded as pure numerics.



         "client_code" as "ClientCode",

         "client_code"&'-'&"suffix_no" as "ContactID",

         "contact_name" as "ContactName",

         keepchar(phone_number, '0123456789') as "ScrubbedNum2";


SQL SELECT * FROM contacttable




SQL SELECT con.client_code, con.ScrubbedNum2 FROM phonecalltable cdr inner join contacttable con on cdr.CallerNum=con.ScrubbedNum2;


Unsurprisingly, it's giving me an invalid column name error on ScrubbedNum2, but that scrubbed number is what I need to use to try and find matches in the contact table.

I KNOW I am missing something (obviously) but I am not sure where I am going wrong...and that this point the load portion has been through a dozen different iterations trying to get it to work, but I think regardless of the data I ask for - the join is what's kicking out the error.

If anyone could point me in the right direction - I'd much appreciate it.

Thanks all!

Tags (2)
1 Reply
Honored Contributor II

Re: Joining tables based on an altered field


Your issue is you are creating the ScrubbedNum2 field in the QlikView LOAD statement, but then tring to join two SQL tables on this field.  SQL has no idea what you are talking about as you created the field in QlikView!!  Try this:



         "client_code"                                         as "ClientCode",

         "client_code"&'-'&"suffix_no"                   as "ContactID",

         "contact_name"                                     as "ContactName",

         keepchar(phone_number, '0123456789')   as "ScrubbedNum2"


SQL SELECT client_code, suffix_no, contact_name, phone_number FROM contacttable;

LEFT JOIN (Contacts)


        CallerNum     AS  ScrubbedNum2,





SQL SELECT CallerNum, Field1, Field2, etc FROM phonecalltable;

This is now performing the join in QlikView and it will join on ALL common field names (in this case just ScrubbedNum2).

Hope this helps,


Community Browser