One to many relationship

[#1]

Hi,

I have two tables, Sales table and Discounts table. 1 transaction can have many different discounts, how can I do this in qlikview because once I add up the extended_price(from Sales Table) it gives me wrong result. What will I do?

[jvitantonio]

In this case you need to use a DISTINCT parameter in your funcion. For instance sum(Distinct Sales)

[#3]

it still give me a wrong result..

[deepakk]

HI,

If you are joining the discount table to the sales, then you need to summarize the discount table based on the One transaction of sales and then join it.

eg:  Load invoice_no, invoice_date, amt from sales;

join

Load invoice_no, Sum(Discount_Amt) as Disc_Amt from Discount Table group by invoice_no;

I hope this helps.

Deepak

[#5]

Sales and Discount table are two different fact tables, when I  sum sales_amount it gives me wrong result because

every sales transaction can have different discounts.

SalesTable:

fact_transactions_id,

sales_amount,

other fields

DiscountTable:

fact_discounts_id

fact_transactions_id,

discount_amount,

other fields

[deepakk]

hi,

if you are loading the table in the above method then Sum(sales_amount) should come correct and if  it is comming wrong it has nothing to do with the discount table.

Load the table alone and check the figure. Are you subtracting discount from sales.

if yes , how are you writing your expression? Try the two below way

1) Sum(sales_amount) - Sum(discount_amount)

or

2)Sum(sales_amount -discount_amount)

if the issue is still there , then attach a sample application so that we can take a close look into the issue.

Deepak

[#7]

Hi Sir,

attached is the qvw file that shows the wrong output.

This should be the correct result. Don't mind the item description because I scrambled the data:

[deepakk]

HI,

Your linking is not correct and hence the Product for each sales is getting multiple due to which you are getting higher figures.

in the link table Try using join the instead of Concatenate. you need to link the salesid with productid and productid

with Product master.

Currenlty the link is not correct.

Deepak

[#9]

Hi Sir,

attached is the qvw file that shows the wrong output.

This should be the correct result. Don't mind the item description because I scrambled the data:

[deepakk]

HI,

I have few queries regarding the data model.

1)why are using the link table . ?

2) Why can't you join the table directly like below

Sales --> Discount using Invoice no

Sales---> ProductMaster  using ProductId

Sales--> Calendar --> using Date.

Deepak