topic lookup or join with different field name in QlikView

lookup or join with different field name

Tue, 15 Feb 2011 14:52:23 GMT

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

lookup or join with different field name

Anonymous — Tue, 15 Feb 2011 15:41:20 GMT

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


Load:
fieldname1 as newfieldphone
fieldname2 as newfieldphone
...

Does that work?

lookup or join with different field name

Tue, 15 Feb 2011 16:33:16 GMT

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.

lookup or join with different field name

Tue, 15 Feb 2011 16:59:47 GMT

Make a new field called LinkNumber in both tables.

lookup or join with different field name

Tue, 15 Feb 2011 17:32:42 GMT


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

lookup or join with different field name

Tue, 15 Feb 2011 17:56:00 GMT

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

lookup or join with different field name

johnw — Wed, 16 Feb 2011 02:05:34 GMT

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.

lookup or join with different field name

Mon, 02 Jan 2012 13:14:49 GMT

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?

lookup or join with different field name

vijay_iitkgp — Tue, 03 Jan 2012 09:02:21 GMT

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