TABLE JOIN PROBLEM..

[#1]

Dear Experts,

I want to make a data model from following tables. 

tables are shown below,(SAMPLE DATA)

BUDGET,

CLA_CODE BUD_MONTH     BUD_YEAR     BUDGET

AA               JAN                    2015               100,000.00

BB               JAN                    2015                 10,000.00

CC               JAN                    2015                 15,000.00

AA               FEB                   2015                120,000.00

BB               FEB                   2015                 11,000.00

CC               FEB                   2015                 14,000.00

AA               MAR                  2015                150,000.00

BB               MAR                  2015                  12,000.00

CC               MAR                  2015                  15,000.00

In this case there are many sub products which mainly not allocated the budgets.

Ex,

CLA_CODE     PRD_CODE

AA                    AA1

AA                    AA2

AA                    AA3

BB                    BB1

BB                    BB2

BB                    BB3

CC                    CC1

CC                    CC2

In the data base records are available as shown below,

DATE          POL_NO          PRD_CODE     SALE_AMOUNT

01/01/15     AA10015001     AA1                     10,000.00

02/01/15     BB10015001     BB1                        1,000.00

03/01/15     CC10015001     CC1                        1,500.00

04/01/15     BB20015001     BB2                        2,500.00

04/01/15     AA10015002     AA1                        5,000.00

05/01/15     CC30015001     CC3                         3,000.00

05/01/15     CC20015001     CC2                         7,500.00

05/01/15     CC10015002     CC1                         2,000.00

06/01/15     BB10015002     BB1                         1,000.00

06/01/15     BB10015003     BB1                          1,200.00

So I'm looking forward a suitable table join to amalgamate the budgeted figures to the database sales records and calculate the Budget Achievement depending on above tables.

Kind attention and reply would be highly appreciated.

Rgds,

Priyantha.

[Kushal_Chawda]

Please see the attached

[#3]

Dear Sunny,

Your presentation is what I expected and correct. But still my one is not achieved the same and what I'm getting is uploaded above. As I'm using PE version I can not open the file you posted.

I don't know what is the mistake I have done.

Thanks & Regards,

Priyantha.

[#4]

Dear Kush,

Sorry, As I'm using PE version I can not open the file you posted.

Anyway thanks lot for the try.

Thanks & Regards,

Priyantha.

[sunny_talwar]

Do you mind putting your qvw here so that we can look at it... just looking at the final output is not helpful in determining what you might be doing wrong.

[Kushal_Chawda]

Data:

LOAD *,monthname(Date#(BUD_MONTH&BUD_YEAR,'MMMYYYY')) as BUD_MONTH_YEAR,

month(Date#(BUD_MONTH,'MMM')) as BUD_MONTH_NEW

Inline [

CLA_CODE, BUD_MONTH,     BUD_YEAR,     BUDGET,

AA,               JAN ,                   2015,               100000.00

BB ,              JAN,                    2015 ,                10000.00

CC,               JAN ,                   2015 ,                15000.00

AA ,              FEB,                   2015 ,               120000.00

BB,               FEB,                   2015 ,                11000.00

CC ,              FEB ,                  2015 ,                14000.00

AA ,              MAR ,                 2015,                150000.00

BB ,              MAR ,                 2015 ,                 12000.00

CC ,              MAR,                  2015  ,                15000.00 ];

Left Join

LOAD * Inline [

CLA_CODE,     PRD_CODE

AA,                    AA1

AA,                    AA2

AA ,                   AA3

BB,                    BB1

BB,                    BB2

BB ,                   BB3

CC ,                   CC1

CC ,                   CC2

CC ,                   CC3 ];

Budget:

NoConcatenate

LOAD CLA_CODE,

BUD_MONTH_NEW as BUD_MONTH,

BUDGET,

PRD_CODE as PRD_CODE_Budget,

AutoNumber(BUD_MONTH_YEAR&trim(PRD_CODE)) as Key

Resident Data;

DROP Table Data;

Production:

LOAD *,AutoNumber( monthname(Date#(DATE,'DD/MM/YY'))& trim(PRD_CODE)) as Key

Inline [

DATE,          POL_NO ,         PRD_CODE,     SALE_AMOUNT

01/01/15,     AA10015001,     AA1,                     10000.00

02/01/15,     BB10015001,     BB1,                        1000.00

03/01/15,     CC10015001,     CC1,                        1500.00

04/01/15,     BB20015001,     BB2,                        2500.00

04/01/15,     AA10015002,     AA1,                        5000.00

05/01/15,     CC30015001,     CC3 ,                        3000.00

05/01/15,     CC20015001,     CC2,                         7500.00

05/01/15,     CC10015002,     CC1,                         2000.00

06/01/15,     BB10015002,     BB1,                         1000.00

06/01/15,     BB10015003 ,    BB1 ,                         1200.00 ];

[#7]

Dear kush,

That is not what I'm expecting.

Rgds,

Priyantha.

[#8]

Dear Sunny,

This is for your reference.

[sunny_talwar]

What is for my reference??? I don't see anything???