Please help :
I wish to load a table from a Warehouse and a table from Excel.
In the script I wish to compare a created field from the Warehouse and a created field from Excel, see script below :
MatchTab: LOAD '00'&[Parent NSC]&num([Account Number],'00000000')&Value as AccountNumber, [Approved? N/Y] /*upper([Customer Name]) as [RCMT Customer Name]*/ FROM [S:\Business Support\Master Log Qlikview.xls] (biff, embedded labels, header is 1 lines, table is RCMT$) Where [Date received by RCMT] >'06/04/2012'; Join LOAD ACC_NO&text((limit_now)*-1) as WHAccount FROM $(vFileDate).qvd (qvd) Where Period_dte >'06/04/2012'; FinTable: LOAD AccountNumber /*as QVAccount*/ , WHAccount /*as MatchWHAccount*/ , if(AccountNumber=WHAccount and [Approved? N/Y]='Yes', 'Yes', 'No') as Matched Resident MatchTab; DROP Table MatchTab;
This should create a field called Matched where if I display as a List box I can tick "Yes" and show all the Matched,
The Errors/Issues I am having are the following :
1) In some instances the AccountNumber and WHAccount would be duplicated , see data below
Using the Match as it is above, it will match the 123 WHACCOUNT with BOTH the ACCOUNTNUMBERS 123, so if I filter in Matched Yes on table AccountNumber I have a result of 2 , Yet on WHACCOUNT result of 1 . I wish a result of 1 on both !!! ( so only matches First instance!!) I have tried variations of distinct and I cant quite get it
2) If I select "No" on the List box, the data sect remains the same, I would require to filter in all accounts that do not match
3) By using the above method I am creating a table that is the Key between the 2 tables
I need an additional Key of Region, If I rename from Table WHAccount field region_name to Region it will create a key with AccountNumber Table but it will be a syn key as I have already created keys. See viewer below
Guys Thanks a mill
Any thoughts or suggestions on any the above would really be greatly appreciated !!