AMALGAMATE 2 TABLES

[upaliwije]

Hi Friends

I have in QV document folloing two tables

Table 1

EMPLOYEE	DESCRIPTION	YEAR	BCOD	SEQ
A	Bank Slip Entry to  the System	2010	8M00	7
B	Bank Slip Entry to  the System	2010	8M00	7
C	Bank Slip Entry to  the System	2011	8M00	7
A	Bank Slip Entry to  the System	2011	8M00	7
B	Bank Slip Entry to  the System	2012	8M00	7
C	Bank Slip Entry to  the System	2012	8M00	7
A	Integration of Debit_Notes	2010	8M00	9
B	Integration of Debit_Notes	2010	8M00	9
C	Integration of Debit_Notes	2011	8M00	9
A	Integration of Debit_Notes	2011	8M00	9
B	Integration of Debit_Notes	2012	8M00	9
C	Integration of Debit_Notes	2012	8M00	9
A	Integration of Receipts	2010	8M00	8
B	Integration of Receipts	2010	8M00	8
C	Integration of Receipts	2011	8M00	8
A	Integration of Receipts	2011	8M00	8
B	Integration of Receipts	2012	8M00	8
C	Integration of Receipts	2012	8M00	8

Table 2

YEAR	BCOD	TYPE	NOS
2010	8M00	DEBIT_NOTES	679
2010	8M00	DIR_RECEIPTS	4508
2011	8M00	DEBIT_NOTES	711
2011	8M00	DIR_RECEIPTS	5263
2012	8M00	DEBIT_NOTES	947
2012	8M00	DIR_RECEIPTS	5587

I want to combine the two table and obtain following output table

EMPLOYEE	DESCRIPTION	YEAR	BCOD	SEQ	TYPE	NOS
A	Bank Slip Entry to  the System	2010	8M00	7	DIR_RECEIPTS	4508
B	Bank Slip Entry to  the System	2010	8M00	7	DIR_RECEIPTS	4508
C	Bank Slip Entry to  the System	2011	8M00	7	DIR_RECEIPTS	5263
A	Bank Slip Entry to  the System	2011	8M00	7	DIR_RECEIPTS	5263
B	Bank Slip Entry to  the System	2012	8M00	7	DIR_RECEIPTS	5587
C	Bank Slip Entry to  the System	2012	8M00	7	DIR_RECEIPTS	5587
A	Integration of Debit_Notes	2010	8M00	9	DEBIT_NOTES	679
B	Integration of Debit_Notes	2010	8M00	9	DEBIT_NOTES	679
C	Integration of Debit_Notes	2011	8M00	9	DEBIT_NOTES	711
A	Integration of Debit_Notes	2011	8M00	9	DEBIT_NOTES	711
B	Integration of Debit_Notes	2012	8M00	9	DEBIT_NOTES	947
C	Integration of Debit_Notes	2012	8M00	9	DEBIT_NOTES	947
A	Integration of Receipts	2010	8M00	8	DIR_RECEIPTS	4508
B	Integration of Receipts	2010	8M00	8	DIR_RECEIPTS	4508
C	Integration of Receipts	2011	8M00	8	DIR_RECEIPTS	5263
A	Integration of Receipts	2011	8M00	8	DIR_RECEIPTS	5263
B	Integration of Receipts	2012	8M00	8	DIR_RECEIPTS	5587
C	Integration of Receipts	2012	8M00	8	DIR_RECEIPTS	5587

Amagamation should be done based on following condition

If seq No= 7 or 9 type should be DIR_RECEIPTS Nos should be value given against DIR_RECEIPTS in table 2

If seq No= 8 type should be DEBIT_NOTES Nos should be value given against DEBIT_NOTES in table 2

To do the above I have tried my best but I am unable to do it. Pls help me to achieve my objective.

[upaliwije]

If I use Apply map technique could this be done ?

[johnw]

Maybe when loading Table 1, also load the TYPE field.

if(match(SEQ,7,9),'DIR_RECEIPTS',if(SEQ=8,'DEBIT_NOTES')) as TYPE,

Then left join Table 2 onto table 1.

[upaliwije]

Tks Jhon

Could You pls write the script  for  left join

[johnw · Accepted solution]

Something like this.

[Table 1]:

LOAD

EMPLOYEE

,DESCRIPTION

,YEAR

,BCOD

,SEQ

,if(match(SEQ,7,9),'DIR_RECEIPTS',if(SEQ=8,'DEBIT_NOTES')) as TYPE

FROM your source for Table 1

;

LEFT JOIN ([Table 1])

LOAD

,YEAR

,BCOD

,TYPE

,NOS

FROM your source for Table 2

;