Merging Different Tables

Fri, 08 Jul 2011 12:36:35 GMT

Dear All,

Kindly give me code for below query. We are working as an intermediate between customer and supplier.

In our system, we have below data for each customer and each supplier..

Customer Table:

Customer ID Customer Name Customer Currency Customer Country

Where Customer ID is unique.

Supplier Table:

Supplier ID Supplier Name Supplier Currency Supplier Country

Supplier ID is unique.

Based on prices available from different suppliers on respective brands, we are sending quotation to our customers. These data are saved in below table. Here Our Part NO is unique but can be associated with more than one brand. We can say one part number is having three or more different quality. Example shown below. But there will be only one Our Part NO for each Customer Part NO

Our Part NO Brand Quoted Price Customer Part NO

ABCXYZ A 10 1234 567

ABCXYZ B 20 1234 567

ABCXYZ C 30 1234 567

Quotation Table:

Customer ID Our Part NO Customer Part NO Brand Quoted Price

Also, for each supplier, we have purchase cost table.

Supplier Price Table:

Supplier ID Our Part NO Brand Supplier Part NO Purchase Cost

Based on our quotations, customers are sending orders as below...

Customer Order Table:

Customer ID Customer Sales Order NO Customer Part NO Brand Qty

Finally we are sending orders to our supplier based on customers' orders. We have below data stored for our Purchases/Sales.

Purchase Order Table:

Purchase Order NO Supplier ID Supplier Part NO Our Part NO Brand Qty Customer ID Customer Sales Order NO

I need code to get table with following columns.

Final Table:

Purchase Order NO

Supplier ID

Supplier Name

Supplier Currency

Supplier Country

Supplier Part NO

Our Part NO

Brand

Qty

Purchase Cost

Purchase Total

Customer ID

Customer Name

Customer Currency

Customer Country

Customer Part NO

Customer Sales Order NO

Quoted Price

Invoice Total

Where Purchase Total = Qty * Purchase Cost and Invoice Total = Qty * Quoted Price

Also, if possible, I want unique line number in Final Table.

Thanks in advance.

Re: Merging Different Tables

Fri, 08 Jul 2011 19:26:40 GMT

Look into (Left/Right/etc) Join Load. Depending on if you're missing some data in fields, you might prefer one type of Join over another. Create a new table by loading from a resident table, and then joining the existing ones (2 at a time): if X is the new table, and you need to combine tables A, B, and C into X - join X + A -> XA, XA + B -> XAB, XAB + C -> XABC.

So you load the needed fields from A into X with "Load ...fields... Resident A;" Then, for example, "Left Join (X) Load ...fields... Resident B;", and so forth.

For the fields that are calculated:

Qty * [Purchase Cost] AS [Purchase Total],

Qty * [Quoted Price] AS [Invoice Total]

Hope this helps,

Siarhei K.

topic Re: Merging Different Tables in QlikView

Merging Different Tables

Re: Merging Different Tables