topic Re: Scripting Help in QlikView

Scripting Help

Anonymous — Wed, 16 Sep 2015 12:38:52 GMT

I have 2 tables. The first has 3 fields:

Date, Supplier, Sales

the second has 3 fields

Date, Customer, Sales

Therefore I know the sales by customer by date and I know the sales by supplier by date and for any day the sum of sales are the same in both tables. I want to create a table that shows the sales by customer AND supplier. How can I do this?

Thanks

Re: Scripting Help

Anonymous — Wed, 16 Sep 2015 12:46:35 GMT

you an combine the two tables

Supplier:

noconcatenate load

Date,

Supplier,

Sales as Supplier.Sales

from ..

Customer:

noconcatenate load

Date,

Customer,

Sales as Customer.Sales

from ..

outer join(Supplier)

load *

resident Customer;

drop table Customer;

rename table Supplier to FactTable;

Re: Scripting Help

sunny_talwar — Wed, 16 Sep 2015 12:47:14 GMT

If I am thinking about this correctly, you might need another key field (May be OrderID) to order to know which supplier sold to which customer. Right now the way your two tables are structured seem to join the two tables on Date and Sales Amount. If for some reasons there are two Suppliers or Two Customers who did same amount of sales on the same day you will end up having a Cartesian product between the two tables. So I think having a OrderID will def. help here.

Re: Scripting Help

Colin-Albert — Wed, 16 Sep 2015 12:53:37 GMT

You can concatenate the two tables to create a single table with 4 columns: Date, Supplier, Customer, Sales

Sales:

load

Date,

Supplier,

Sales

from ..

concatenate(Sales)

load

Date,

Customer,

Sales

from ..