topic Re: Merge update Resident tables in QlikView

Merge update Resident tables

Wed, 21 Sep 2011 16:40:07 GMT

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

Merge update Resident tables

its_anandrjs — Wed, 21 Sep 2011 16:49:05 GMT

Hi,

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

Rgds

Anand

Re: Merge update Resident tables

its_anandrjs — Wed, 21 Sep 2011 16:55:53 GMT

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

Re: Merge update Resident tables

its_anandrjs — Wed, 21 Sep 2011 17:03:03 GMT

Hi,

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

let me know about this.

HTH

Rgds

Anand

Re: Merge update Resident tables

its_anandrjs — Wed, 21 Sep 2011 17:14:05 GMT

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

Re: Merge update Resident tables

Wed, 21 Sep 2011 18:08:04 GMT

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

Re: Merge update Resident tables

jonathandienst — Wed, 21 Sep 2011 18:26:23 GMT

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

Re: Merge update Resident tables

its_anandrjs — Wed, 21 Sep 2011 18:50:47 GMT

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.

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

Re: Merge update Resident tables

its_anandrjs — Wed, 21 Sep 2011 19:23:32 GMT

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

Re: Merge update Resident tables

Wed, 21 Sep 2011 19:37:15 GMT

Thank You Jonathan & Anand!

Re: Merge update Resident tables

jonathandienst — Wed, 21 Sep 2011 19:37:36 GMT

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

Re: Merge update Resident tables

Wed, 21 Sep 2011 19:49:21 GMT

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;

Re: Merge update Resident tables

jonathandienst — Wed, 21 Sep 2011 20:42:30 GMT

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

Jonathan

Re: Merge update Resident tables

Wed, 21 Sep 2011 20:47:36 GMT

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.