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.

[alexandros17]

It should be quite simple,

load first table

left join

load second table

left join

load third table

in this way you have linked all your information, now in a straight or pivot table use

CLA_CODE     PRD_CODE DATE and POL_NO as dimension and

write

SUm(SALE_AMOUNT) as expression

let me know

[#3]

Dear Alessandro,

Output table is shown like below.

This is not the expected output.

[#4]

Budget figures are not shown  as expected.

Try to help.

[#5]

Dear Alessandro,

I don't have budgets for Products. only available for Classes, which includes several products. But in my table budgeted figures are shown.

Something wrong in my table join. Please help me to solve this.

Rgds.

Priyantha.

[#6]

Dear All,

Any genius idea to take correct value for "BUDGET".....???

PRIYANTHA

[sunny_talwar]

Is this the table you are looking for?

Script:

Table:

LOAD * Inline [

CLA_CODE, PRD_CODE

AA,        AA1

AA,        AA2

AA,        AA3

BB,        BB1

BB,        BB2

BB,        BB3

CC,        CC1

CC,        CC2

];

Join(Table)

LOAD *,

  MonthName(DATE) as MonthYear;

LOAD * Inline [

DATE,          POL_NO,          PRD_CODE,    SALE_AMOUNT

01/01/15,    AA10015001,    AA1,            10000

02/01/15,    BB10015001,    BB1,            1000

03/01/15,    CC10015001,    CC1,            1500

04/01/15,    BB20015001,    BB2,            2500

04/01/15,    AA10015002,    AA1,            5000

05/01/15,    CC30015001,    CC3,            3000

05/01/15,    CC20015001,    CC2,            7500

05/01/15,    CC10015002,    CC1,            2000

06/01/15,    BB10015002,    BB1,            1000

06/01/15,    BB10015003,    BB1,            1200

];

Join(Table)

LOAD *,

  MonthName(Date#(Capitalize(BUD_MONTH) & '-' & BUD_YEAR, 'MMM-YYYY')) as MonthYear;

LOAD * Inline [

CLA_CODE, BUD_MONTH,    BUD_YEAR,    BUDGET

AA,      JAN,          2015,        100000

BB,      JAN,          2015,        10000

CC,      JAN,          2015,        15000

AA,      FEB,          2015,        120000

BB,      FEB,          2015,        11000

CC,      FEB,          2015,        14000

AA,      MAR,          2015,        150000

BB,      MAR,          2015,        12000

CC,      MAR,          2015,        15000

];

[#8]

THIS IS WHAT I GOT WITH YOUR SCRIPT

CAN YOU PLEASE CHECK THIS,

RGDS,

PRIYANTHA.

[sunny_talwar]

Right, I am not sure what you are expected output is, So I would rather ask you to check if this is the output you are expecting? If not what exactly are you trying to achieve???

[qlikviewwizard]

Good one sunindia