Joining tables based on an altered field

[raynac]

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

http://community.qlik.com/thread/55546

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.

--------------------------------------------------

load

         "client_code" as "ClientCode",

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

         "contact_name" as "ContactName",

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

SQL SELECT * FROM contacttable

load

         callerNum;

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!

[Jason_Michaelides]

Hi,

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:

Contacts:

LOAD

         "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)

LOAD

        CallerNum     AS  ScrubbedNum2,

        Field1,

        Field2,

        etc

;

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,

Jason