Script load - join/primary key question

[#1]

I have a budget table and a sales table. Both tables have cost center, amount, month and year. There are items in the budget table that have no match in the sales table and there are sales items in the sales table that have no match in the budget table. I need to compare sales to budget and need to show all records. Do I need to create a linking table or is there other ways with joins or creating key or ??? How would I connect to two tabes to get all possible combinations?

Thanks,

Stephen

[#2]

You have a sales-date?

Create a key in the sales table:

year(salesdate)&'-'&month(salesdate) as key

and in the budget table

budgetyear&'-'&budgetmonth as key

Have Fun! - Regards!

Dirk Rönsch

[#3]

Should have given more information. The Sales table has multiple records (its a detail table) that may relate to one record in the Budget table.

[rwunderlich · Accepted solution]

Probably the best approach is to CONCATENATE Sales and Budget into a single table.

-Rob

[rwunderlich]

sjcharles wrote:The Sales table has multiple records (its a detail table) that may relate to one record in the Budget table.

Here's an extended explanation of how to use the CONCATENATE approach.

http://qlikviewnotes.blogspot.com/2009/11/understanding-join-and-concatenate.html

-Rob

[#6]

Thanks for your example.

However, in my case add "Central 2009 $5,000" to your Budget table (no match in Sales table) and add "Export 2009 Toys $1,000" to your Sales table (no match in Budget table). That is the situation I have. and all needs to show up in a table/graph.

Stephen

[rwunderlich]

I don't see the additional data changing the problem or the solution. Attached is the example qvw from the blog post with your additional unmatched data. And here's a screen shot of the main chart. Is that what you are expecting to see?

-Rob

[#8]

You are right, the addition data does not make difference.Concatenate works fine.

Thanks,

Stephen