Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

TABLE JOIN PROBLEM..

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.

17 Replies
alexandros17
Partner - Champion III
Partner - Champion III

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

Not applicable
Author

Dear Alessandro,

Output table is shown like below.

This is not the expected output.

Not applicable
Author

Budget figures are not shown  as expected.

Try to help.

Not applicable
Author

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.

Not applicable
Author

Dear All,

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

PRIYANTHA

sunny_talwar

Is this the table you are looking for?

Capture.PNG

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

];

Not applicable
Author

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
Master II
Master II

Good one sunindia