topic Concatenating two tables in QlikView

Concatenating two tables

Thu, 29 Sep 2011 09:49:40 GMT

Hi All,

Table1:

InvooiceNo	CustCode	ProdCode
1	10	100
2	20	200
3	30	300
4	40	400

Table2:

InvoiceNo	CustCode	ProdCode
1	10	100
2	20	200
9	90	300
8	80	400
5	50	500
6	60	600
7	70	700

Table3:

InvoiceNo	Table1.CustCode	Table2.CustCode
1	10	10
2	20	20
3	30	-
4	40	-
5		50
6		60
7		70
8		80
9		90

how to achieve Table3 from table 1 and table2?

Thanks.

Re: Concatenating two tables

its_anandrjs — Thu, 29 Sep 2011 10:02:53 GMT

Hi,

See the attached sample file.

HTH

Rgds

Anand

Concatenating two tables

kaushiknsolanki — Thu, 29 Sep 2011 10:07:48 GMT

Hi,

Try this code.

Data1:

load * inline [
InvoiceNo, CustCode, ProdCode
1, 10, 100
2, 20, 200
3, 30, 300
4, 40, 400
];

Data2:

Load * inline [
InvoiceNo, CD, PD
1, 10, 100
2, 20, 200
9, 90, 300
8, 80, 400
5, 50, 500
6, 60, 600
7, 70, 700
];

Regards,

Kaushik Solanki

Re: Concatenating two tables

rohit214 — Thu, 29 Sep 2011 10:45:07 GMT

hey sandeepa

plaese find the attachment.

thanks

rohit

Re: Concatenating two tables

Thu, 29 Sep 2011 13:02:16 GMT

Thanks all for the response.

Suppose I have:

InvooiceNo	CustCode	ProdCode
1	10	100
2	20	200
3	30	300
4	40	400

Table2:

InvoiceNo	CustCode	ProdCode
1	10	100
2	20	200
9	90	900
8	80	800
5	50	500
6	60	600
7	70	700

Table3:

ProdCode	BrandCode	BrandName
100	000	xxx
200	001	yyy
300	002	zzz
400	003	aaa
500	004	bbb
600	005	ccc
700	006	ddd
800	007	eee
900	008	fff
1000	009	ggg

What I have to do is If I click on BrandName it should provide me the details of prodcodes and customers and differences as well.

Here count(tab1.custcode)=4

and count(tab2.custcode)=7.

Matched is 2. Suppose A=Count(Tab1.CustCode) and B=Count(Tab2.CustCode). I also have to show A-B and B-A.

Please Suggest.

Re: Concatenating two tables

its_anandrjs — Thu, 29 Sep 2011 14:17:59 GMT

Hi,

You need to load a data some thing like that the InvoiceNo connected to each other among table and also Productcode will be connected to each other then you get difference like

A-B = 2

B-A = 7

Rgds

Anand

Concatenating two tables

SunilChauhan — Thu, 29 Sep 2011 14:26:34 GMT

Join table A and Table 2

and link table 3

and calulate what eveer you want

Concatenating two tables

SunilChauhan — Thu, 29 Sep 2011 14:27:43 GMT

sorry

Join table A and Table 2

and link to table 3 by ProdCode

and calulate what eveer you want

Re: Concatenating two tables

Thu, 29 Sep 2011 14:54:30 GMT

Hi,

Can you please provide me with a qvd model.

Thanks.

Concatenating two tables

SunilChauhan — Thu, 29 Sep 2011 14:58:35 GMT

table1:

Load

field1,

field2,

from table1;

table2:

Join(table1)

Load

field1,

field2,

from

table2;

table3:

Load

field1,

field2,

from table3;

Concatenating two tables

SunilChauhan — Thu, 29 Sep 2011 15:02:41 GMT

or in exact columns only need to change path i believee

table1:

Load

InvooiceNo,

CustCode,

ProdCode

from table1;

table2:

Join(table1)

Load

InvooiceNo,

CustCode,

ProdCode

from table2;

table3:

Load

ProdCode,

BrandCode ,

BrandName

from table3;

Re: Concatenating two tables

Thu, 29 Sep 2011 15:18:09 GMT

Hi Sunil,

Please Find attached of my qvd file.

Thanks,

Sandeepa

Concatenating two tables

SunilChauhan — Thu, 29 Sep 2011 15:31:24 GMT

In Data Tab in script

use this

IMSIRR:
LOAD CustCode,
     TransType,
     InvoiceNo,
     InvoiceDate,
     ProdCode,
     Qty,
     Amount,
     InvType,
     Week(MakeDate(Left( InvoiceDate,4), Mid(InvoiceDate, 5, 2), Right(InvoiceDate,2))) as Week,
     'IMS' as Source
FROM
C:\OwnExtractImsFiles\FullDataofIMSdonotdelete.txt
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

Join(IMSIRR)
LOAD CustCode,
     TransType,
     InvoiceNo,
     InvoiceDate,
     ProdCode,
     Qty,
     LineValue,
     Type as InvType,
     Week(MakeDate(Left( InvoiceDate,4), Mid(InvoiceDate, 5, 2), Right(InvoiceDate,2))) as Week,
     'IRR' as Source
FROM

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

hope this helps

Re: Concatenating two tables

its_anandrjs — Thu, 29 Sep 2011 15:36:11 GMT

Hi,

I think you have to load data some thing in sample file.

See the attached

Rgds

Anand