Merge update Resident tables

[#1]

Hello,

Is there any way I could combine three qvds in to a single table?

My first table stores 2009_Sales, Second 2010_Sales and thrid 2011_sales

I want to create a Single table  with all three year sales reported as seperate column with out the account being duplicated

ex. if Account A077 is present in 2009_sales and 2011_sales (not in 2010_sales) I need to create only one record with 2009 & 2011 sales and then put 0 for 2010 . Also I would like to add two columns 1.100_SALE (If there a $100 sale in any of the year value would be Y) and 2.Total (2009_sales+2010_Sales+2011_sales)

Thanks in advance,

A.Paul

2009_SALES_TABLE
Account	Contact	2009Sales
A001	Bill	$100
A002	Mark	$70
A003	Steve	$25
A004	Susie	$175
A005	James	$560

2010_SALES_TABLE
Account	Contact	2010Sales
A001	Bill	$230
A002	Mark	$590
A003	Steve	$80
A008	Jodi	$100
A009	Job	$100
A010	Ronnie	$20
2011_SALES_TABLE
Account	Contact	2011Sales
A001	Bill	$40
A002	Mark	$200
A009	Job	$100
A006	Elvis	$10
A007	Mary	$5
A004	Susie	$200

Final_TABLE
Account	Contact	100_SALE	2009Sales	2010Sales	2011Sales	ToatlSales
A001	Bill	Y	$100	$230	$40	$370
A002	Mark	Y	$70	$590	$200	$860
A003	Steve	N	$25	$80	$0	$105
A004	Susie	Y	$175	$0	$200	$375
A005	James	Y	$560	$0	$0	$560
A006	Elvis	N	$0	$0	$10	$10
A007	Mary	N	$0	$0	$5	$5
A008	Jodi	Y	$0	$100	$0	$100
A009	Job	Y	$0	$100	$100	$200
A010	Ronnie	N	$0	$20	$0	$20

[jonathandienst]

Anand

Your solution will lose any account/contact combinations that do not exist in all three years, such as James Elvis and Mary because you are using inner joins.

In this sort of case, it is better to create a master table of the keys and then left join into that table, which ensures that no account/contact combinations get lost.

Jonathan

[#3]

Jonathan,

Looks like it did load all 10 accounts.

Thanks,

Jonathan Dienst wrote:

Anand

Your solution will lose any account/contact combinations that do not exist in all three years, such as James Elvis and Mary because you are using inner joins.

In this sort of case, it is better to create a master table of the keys and then left join into that table, which ensures that no account/contact combinations get lost.

Jonathan

Here is what in my final script

TEMP_TAB:

LOAD Account,
Contact,
[2009Sales]
FROM
[\\010 QV\REF\Concat_Data.xls]
(biff, embedded labels, table is [2009$]);

Join(TEMP_TAB)
LOAD Account,
Contact,
[2010Sales]
FROM
[\\010 QV\REF\Concat_Data.xls]
(biff, embedded labels, table is [2010$]);

Join(TEMP_TAB)
LOAD Account,
Contact,
[2011Sales]
FROM
[\\010 QV\REF\Concat_Data.xls]
(biff, embedded labels, table is [2011$]);


Final_Tab:
load *,
(if(isnull([2009Sales]),0,[2009Sales])+if(isnull([2010Sales]),0,[2010Sales])+if(isnull([2011Sales]),0,[2011Sales])) as TOTAL,
if(([2009Sales]>=100 OR [2010Sales]=100 OR [2011Sales]=100), 'Y', 'N') as [100_SALE]
RESIDENT TEMP_TAB;

DROP TABLE TEMP_TAB;

[jonathandienst]

Oops, my mistake. An unqualified join is an outer join - I thought that it was an inner join.

Jonathan

[#5]

Jonathan Dienst wrote:

Oops, my mistake. An unqualified join is an outer join - I thought that it was an inner join.

Jonathan

I am very new to QlikView and truly appreciate all the help you folks provide.