Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
vinaypurohit
Contributor
Contributor

Unable to Link Two Tables of Different Database

Hi,

I am new to QlickView. I have two tables of different database. Both the table have two common columns. 

First Table have :

ItemCode, BatchNumber, TotalPrice

Second Table have:

ItemCode, BatchNumber, Cost

 

I want a result in Table Box :

ItemCode, BatchNumber, TotalPrice, Cost

 

I am able to fetch the Cost for few rows, rest of the rows i am getting NULL value even Cost price is available for that batch.

 

 

 

 

1 Solution

Accepted Solutions
Arthur_Fong
Partner - Specialist III
Partner - Specialist III

Sure.... as long both columns are text field

View solution in original post

8 Replies
chipinapi
Contributor
Contributor

You have synthetic keys pls check...

vinaypurohit
Contributor
Contributor
Author

Hi chipinapi,

Thanks for your reply. Please help how can i remove synthetic key so that i can get correct result. I am new to QlickView and not aware about it. After removing it would i able to link both the tables of different database as explained.

Thanks Again,

Vinay Purohit

 

Arthur_Fong
Partner - Specialist III
Partner - Specialist III

Can you try to load the stock table first and attach the table here?

Just the stock table without bill register table.

 

smallbee001
Contributor
Contributor

you could make a combined group like group1 contains ItemCode, BatchNumbe
vinaypurohit
Contributor
Contributor
Author

I have attached Stock Table.

Thanks.

Arthur_Fong
Partner - Specialist III
Partner - Specialist III

Here's the catch:

In your registry table, Batch is text format.

In stock table , Batch is numeric.

When QV creates association, it is not able to link these two columns with different formats together.

 

Try this script after you have loaded your stock table.

tempHMS_STOCKGRNITEMS:
load text(Code)as Code, Batch ,Cost
resident "HMS_STOCKGRNITEMS(NOLOCK)";

drop table "HMS_STOCKGRNITEMS(NOLOCK)";

 

Thanks and regards,

Arthur Fong

vinaypurohit
Contributor
Contributor
Author

Thank you Very Much Arthur Fong,

This script is not working, may i convert the batch to VARCHAR in SQL statement using Convert() function like:-

SELECT Distinct GI.ITEMDR "Code",
CONVERT(VARCHAR(20),GB.BatchNo) "Batch"

Regards,

Vinay Purohit

Arthur_Fong
Partner - Specialist III
Partner - Specialist III

Sure.... as long both columns are text field