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

[its_anandrjs]

Hi,

Yes it is possible to join this qvds you need to concatenate this qvds.

Rgds

Anand

[its_anandrjs]

Hi,

Load tables like

T1:

Load

from table1;

concatenate(T1)

Load

from table2;

concatenate(T1)

Load

from table3;

And use isnull( )  function to remove null values like

if(isnull([2010Sales]) = -1,0,[2010Sales])

Rgds

Anand

[its_anandrjs]

Hi,

See the attached sample file how to load and use isnull( ) function.

let me know about this.

HTH

Rgds

Anand

[its_anandrjs]

Hi,

See the attached sample file how to load and use isnull( ) function.

and compute

to check the 100 sale

If( [2009Sales]+[2010Sales]+[2011Sales] >=100,'Y','N')

HTH

Rgds

Anand

[#6]

Anand,Thanks for the quick responses

BTW it creates duplicate entries in the table. Is there any way we could eliminate the duplicates?

example: Merge the first three rows in to one?

Account	Contact	2009Sales	2010Sales	2011Sales
A001	Bill	$100.00
A001	Bill		$230.00
A001	Bill			$40.00
A002	Mark	$70.00
A002	Mark		$590.00
A002	Mark			$200.00
A003	Steve	$25.00
A003	Steve		$80.00
A004	Susie	$175.00
A004	Susie			$200.00
A005	James	$560.00
A006	Elvis			$10.00
A007	Mary			$5.00
A008	Jodi		$100.00
A009	Job		$100.00
A009	Job			$100.00
A010	Ronnie		$20.00

[jonathandienst]

Hi

Perhaps a better way to cahieve this is with joins rather than concatenation. The concat will create those null records. This is how to do it using joins:

temp:
LOAD Account, 
     Contact
FROM DD.xlsx (ooxml, embedded labels, table is Sheet1);
Concatenate
LOAD Account, 
     Contact
FROM DD.xlsx (ooxml, embedded labels, table is Sheet2);
Concatenate
LOAD Account, 
     Contact
FROM DD.xlsx (ooxml, embedded labels, header is 1 lines, table is Sheet3);
DataTable:
NoConcatenate
LOAD Distinct * Resident temp;
DROP Table temp;
Left Join (DataTable)
LOAD Account, 
     Contact, 
     [2009Sales]
FROM DD.xlsx (ooxml, embedded labels, table is Sheet1);
Left Join (DataTable)
LOAD Account, 
     Contact, 
     [2010Sales]
FROM DD.xlsx (ooxml, embedded labels, table is Sheet2);
Left Join (DataTable)
LOAD Account, 
     Contact
FROM DD.xlsx (ooxml, embedded labels, header is 1 lines, table is Sheet3);

The first loads ensure that the result table contains all possible combinations of account and contact. The subsequent loads "join" the YYYYSales columns to the data table.

Hope that helps

Jonathan

[its_anandrjs · Accepted solution]

Hi Ajipaul,

Thanks very much ok no problem to achieve this you make joins between this tables and load them so no duplicate values comes.

But in qlikview we can achive this type of problem after load also. We have to do in place of Concate use Join command.

Like

T1:

LOAD Account,

     Contact,

     [2009Sales]

FROM

DD.xlsx

(ooxml, embedded labels, table is Sheet1);

Join(T1)

LOAD Account,

     Contact,

     [2010Sales]

FROM

DD.xlsx

(ooxml, embedded labels, table is Sheet2);

Join(T1)

LOAD Account,

     Contact,

     [2011Sales]

FROM

DD.xlsx

(ooxml, embedded labels, header is 1 lines, table is Sheet3);

Let me know about this.

HTH

Rgds

Anand

[its_anandrjs]

Hi,

Hope you got correct answer, If you got correct answer so mark it correct under my post.

Let me know about this.

Rgds

Anand

[#10]

Thank You Jonathan  & Anand!