topic Re: Help Required To Join Tables in QlikView

Help Required To Join Tables

Sun, 18 Nov 2012 14:01:45 GMT

Dear All.

I have two tables 1)Invoice Master 2)Invoice Details

the common fields are TransactionKey, RouteKey, VisitKey.

in invoice Master the field TransactionKey is the Primary key.

and Routekey,visitKey are not the primarykeys.

Invoice master table contain 2000 Records

and in Invoice Details no primary key is there.

Invoice Details table contain 5000 Records

i want to join these both tables.

i created one key which is combination of the three common fields

TransactionKey&'_'&RouteKey&'_'&VisitKey as Key,

i inner join both the tables by using the above key.

my result table contain 7000 records.

Is this the correct approach?

Now, I want to join one more table to the resultant table using the ItemCode field (from Invoice Details table) which is now a part of the result table.

ItemCode is not a primary key.

How Can I join these tables in the best way without primary keys? Please suggest.

Thanks,

Mukram.

Re: Help Required To Join Tables

its_anandrjs — Sun, 18 Nov 2012 14:09:23 GMT

Hi,

You doing correct but i suggest join the table simply without the key.

But can you explain how many tables you have and what are the primary keys on the table and how many rest of the fields available in the tables. Please it will go easy and provide the structure.

Regards,

Anand

Re: Help Required To Join Tables

Gysbert_Wassenaar — Sun, 18 Nov 2012 14:15:25 GMT

If your result table contains 7000 rows then probably something went wrong. It sounds like the tables were concatenated instead of joined. If every invoice detail belongs to only one invoice then the number of records in the joined table should be 5000. Can you post the qvw you're working on? Or at least the fields in your tables and the load script.

Re: Help Required To Join Tables

Sun, 18 Nov 2012 14:35:41 GMT

Dear Anand.

Thanks for your quick reply.

1) Invoice master ---Primary key --TransactionKey

total number of fields 10

2) Invoice Detail . (No primary Key)

total Number of fields 20

3) ItemMaster table contain 5 fileds. (No primary key).

Thanks,

Mukram.

Re: Help Required To Join Tables

Sun, 18 Nov 2012 14:40:16 GMT

Dear Gysbert Wassenaar,

Thanks for your reply.

my Script:

InvoiceHeader:

LOAD TransactionKey,

RouteKey,

VisitKey,

TransactionKey&'_'&RouteKey&'_'&VisitKey as Key,

DocumentNumber,

InvoiceNumber,

TransactionDate,

TransactionTime,

DSDNumber,

PONumber,

CustomerCode,

RouteCode,

SalesmanCode,

TotalReplacementAmount

FROM

$(vSource)Table Structure.xlsx

(ooxml, embedded labels, table is InvoiceHeader)

where VoidFlag<>1;

Inner Join(InvoiceHeader)

LOAD RouteKey as InvoiceDetail_RouteKey,

VisitKey as InvoiceDetail_VisitKey,

TransactionKey as InvoiceDetail_TransactionKey,

TransactionKey&'_'&RouteKey&'_'&VisitKey as Key,

ItemCode as ActualItemCode,

ItemCode,

SalesQty,

ReturnQty,

DamagedQty,

FreeSampleQty,

SalesPrice,

ReturnPrice,

StdSalesPrice,

StdReturnPrice,

PromoQty,

SalesCasePrice,

ReturnCasePrice,

StdSalesCasePrice,

StdReturnCasePrice,

GoodReturnPrice,

GoodReturnCasePrice,

StdGoodReturnCasePrice,

StdGoodReturnPrice,

ExpiryQty,

ReturnFreeQty,

ManualFreeQty,

LimitedFreeQty,

RebateRentQty,

FixedRentQty

FROM

$(vSource)Table Structure.xlsx

(ooxml, embedded labels, table is InvoiceDetail);

Inner Join(InvoiceHeader)

LOAD ActualItemCode,

AlternateCode,

AlternateItemGroupCode,

ItemSubGroupCode,

ItemSubGroupAlternateCode,

ItemSubGroupDescription

FROM

$(vdestination)ItemMaster_NewOne.qvd

(qvd);

Thanks,

Mukram

Re: Help Required To Join Tables

Sun, 18 Nov 2012 17:07:11 GMT

Dear,

Yes I'm getting less than 5000 records.

But is that procedure is correct to join ItemMaster_NewOne with InvoiceHeader?

Thanks,

Mukram.