Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
Highlighted
vinaypurohit
New 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
Highlighted
Partner
Partner

Re: Unable to Link Two Tables of Different Database

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

View solution in original post

8 Replies
Highlighted
chipinapi
New Contributor

Re: Unable to Link Two Tables of Different Database

You have synthetic keys pls check...

Highlighted
vinaypurohit
New Contributor

Re: Unable to Link Two Tables of Different Database

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

 

Highlighted
Partner
Partner

Re: Unable to Link Two Tables of Different Database

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

Just the stock table without bill register table.

 

Highlighted
smallbee001
New Contributor

Re: Unable to Link Two Tables of Different Database

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

Re: Unable to Link Two Tables of Different Database

I have attached Stock Table.

Thanks.

Highlighted
Partner
Partner

Re: Unable to Link Two Tables of Different Database

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

Highlighted
vinaypurohit
New Contributor

Re: Unable to Link Two Tables of Different Database

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

Highlighted
Partner
Partner

Re: Unable to Link Two Tables of Different Database

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

View solution in original post