topic AW:Script load - join/primary key question in QlikView

Script load - join/primary key question

Thu, 19 Nov 2009 22:35:51 GMT

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

AW:Script load - join/primary key question

Thu, 19 Nov 2009 23:32:59 GMT

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

AW:Script load - join/primary key question

Fri, 20 Nov 2009 04:40:39 GMT

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.

AW:Script load - join/primary key question

rwunderlich — Fri, 20 Nov 2009 05:22:24 GMT

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

-Rob

AW:Script load - join/primary key question

rwunderlich — Sat, 21 Nov 2009 01:28:07 GMT


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

AW:Script load - join/primary key question

Sat, 21 Nov 2009 04:00:22 GMT

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

AW:Script load - join/primary key question

rwunderlich — Sat, 21 Nov 2009 05:54:21 GMT

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

AW:Script load - join/primary key question

Mon, 23 Nov 2009 23:44:42 GMT

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

Thanks,

Stephen