topic Re: TABLE JOIN PROBLEM.. in QlikView

TABLE JOIN PROBLEM..

Fri, 24 Jul 2015 07:51:28 GMT

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.

Re: TABLE JOIN PROBLEM..

alexandros17 — Fri, 24 Jul 2015 07:55:23 GMT

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

Re: TABLE JOIN PROBLEM..

Fri, 24 Jul 2015 09:51:18 GMT

Dear Alessandro,

Output table is shown like below.

This is not the expected output.

Re: TABLE JOIN PROBLEM..

Fri, 24 Jul 2015 10:00:22 GMT

Budget figures are not shown as expected.

Try to help.

Re: TABLE JOIN PROBLEM..

Mon, 27 Jul 2015 03:10:04 GMT

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.

Re: TABLE JOIN PROBLEM..

Mon, 27 Jul 2015 09:34:05 GMT

Dear All,

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

PRIYANTHA

Re: TABLE JOIN PROBLEM..

sunny_talwar — Mon, 27 Jul 2015 10:45:14 GMT

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

];

Re: TABLE JOIN PROBLEM..

Mon, 27 Jul 2015 11:19:36 GMT

THIS IS WHAT I GOT WITH YOUR SCRIPT

CAN YOU PLEASE CHECK THIS,

RGDS,

PRIYANTHA.

Re: TABLE JOIN PROBLEM..

sunny_talwar — Mon, 27 Jul 2015 11:32:33 GMT

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???

Re: TABLE JOIN PROBLEM..

qlikviewwizard — Mon, 27 Jul 2015 11:53:36 GMT

Good one sunindia

Re: TABLE JOIN PROBLEM..

Kushal_Chawda — Mon, 27 Jul 2015 11:56:01 GMT

Please see the attached

Re: TABLE JOIN PROBLEM..

Mon, 27 Jul 2015 12:05:12 GMT

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.

Re: TABLE JOIN PROBLEM..

Mon, 27 Jul 2015 12:08:04 GMT

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.

Re: TABLE JOIN PROBLEM..

sunny_talwar — Mon, 27 Jul 2015 12:08:30 GMT

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.

Re: TABLE JOIN PROBLEM..

Kushal_Chawda — Mon, 27 Jul 2015 12:11:37 GMT

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 ];

Re: TABLE JOIN PROBLEM..

Tue, 28 Jul 2015 02:59:28 GMT

Dear kush,

That is not what I'm expecting.

Rgds,

Priyantha.

Re: TABLE JOIN PROBLEM..

Tue, 28 Jul 2015 03:11:49 GMT

Dear Sunny,

This is for your reference.

Re: TABLE JOIN PROBLEM..

sunny_talwar — Tue, 28 Jul 2015 10:19:21 GMT

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