lookup or join with different field name

[#1]

Hi,

I have the following problem -

I need to link two fields having different names, but similar content and type with a reference table.

Example: There is reference table that contains details of mobile phones, and has a field called 'Phone Model Name'

In another transactions table, I have two columns, one called 'Old Phone Model Name' and the other 'New Phone Model Name'.Both these fields need to link up with the 'Phone Model Name' column in the reference table so that I can show phone details, for new and old phones.

How do I achieve this? I have the flexibility of changing the column names in the two tables, as well as adding more fields to either of them. (but want to keep a separate reference table)

Please help.

Regards

Amit

[Anonymous]

Just try to rename both fields in the loading script to the same name:

Load:
fieldname1 as newfieldphone
fieldname2 as newfieldphone
...

Does that work?

[#3]

my requirement is also to plot a grid chart of new phone models vs. old phone models.

If I rename the columns names in QV, this wouldn't be rendered correctly.

[#4]

Make a new field called LinkNumber in both tables.

[#5]

[mobile phones]:
LOAD
 ...
 [Phone Model Name]
 ...;
Left Join ([mobile phones])
LOAD
 [Old Phone Model Name] as [Phone Model Name],
 ....;

[#6]

Guys, thanks for your replies. However, it does not address the requirement. Let it elaborate more.

I have a table named "mobile phones" in which each record contains the details (like phone specifications) or a particular phone. The column named 'phone name' identifies the phone.

So, typical records would be:

Phone Name | Manufacturer | OS | Type |

iPhone 4 | Apple | iOS | Smartphone

Nokia N8 | Nokia | Symbian| Smartphone

Nokia 1100 | Nokia | NA | Featurephone

I have another table which contains records indicating phone upgrade requests - where subscribers want to replace existing phones with newer models. Typical records would be -:

Date | Old Phone Name | New Phone Name | Customer ID

1 Jan | Nokia 1100 | Nokia N8 | A001

2 Jan | Blackberry storm | Blackberry Torch | A002

2 Jan | iPhone 3GS | iPhone 4 | A003

Now, i need to show a grid on 'Old Phone Name' vs. ' New Phone Name' and also link these two fields with the 'mobile phones' table so that I can show the details of new and old phones.

Please help.

Regards

[johnw]

One way would be to add a linkage table:

UpgradesVsPhones:
LOAD
Upgrade
,'New' as OldNew
,"New Phone Name" as "Phone Name"
RESIDENT Upgrades
;
CONCATENATE (UpgradesVsPhones)
LOAD
Upgrade
,'Old' as OldNew
,"Old Phone Name" as "Phone Name"
RESIDENT Upgrades
;

See attached. I put the details in a pivot table. I also made a grid chart of the count of the number of upgrades by old vs. new phone name, but I'm not sure if it's what you meant or not.

[#8]

Hi
I have also same problem joining two tables.Could you please show me a way to code it?

Table A:

ID Name  start_date end_date flag
1 abc   xxx         xxxx            Y
2 xyz     xxx             xxxx            Y

Table B:

ID Given_By  Given_To   Amount    Date
1       1         2          200       xxxx

I want as follows:

Temp:
ID Given_By Given_To  Amount
1 abc  xyz  200

How can I achieve this?

[vijay_iitkgp]

Hi,

I think you also need the intervalmatch for this code because your master is showing date range . Please find below the code for same:

T1:
LOAD * INLINE [
    ID, Name, Start_Date, End_Date, Flag
    1, abc,01/01/2012,01/01/2015,Y
    2,xyz,01/01/2012,01/01/2015,Y
];

T2:
LOAD * INLINE [
    ID, Given_By, Given_To, Amount, Date
    1, 1,2,500,07/01/2012,Y
  
];

T3:
NoConcatenate
Load
*
Resident T2;
Left Join IntervalMatch(Date,Given_By)
Load
Start_Date,
End_Date,
ID AS Given_By
Resident T1;
Left Join
LOAD
Start_Date,
ID AS Given_By,
Name AS Given_By_Name 
Resident T1;

Left Join IntervalMatch(Date,Given_To)
Load
Start_Date AS Start_Date1,
End_Date AS End_Date1,
ID AS Given_To
Resident T1;
Left Join
LOAD
Start_Date AS Start_Date1,
ID AS Given_To,
Name AS Given_To_Name
Resident T1;
Drop Table T1,T2;

Hope this will help you.

regards

VIjay