topic Re: Join 1:n in QlikView

Join 1:n

andrew001 — Thu, 25 Oct 2012 15:29:34 GMT

I've two table that I joined with left join (1.N)

I'd like to use the Result_table in a pivot table: if I SUM with the MOV column is OK, but I don't know how I have to do with SALDO column, because I need the only first value by CONTO_KEY

Thank you

TAB_SALDI

CONTO_KEY OWNER SALDO

10001EUR XYZ 10000

TAB_MOV

CONTO_KEY OWNER MOV

10001EUR XYZ 200

10001EUR XYZ 300

10001EUR XYZ 500

RESULT_TABLE

CONTO_KEY OWNER SALDO MOV

10001EUR XYZ 10000 200

10001EUR XYZ 10000 300

10001EUR XYZ 10000 500

Re: Join 1:n

MayilVahanan — Thu, 25 Oct 2012 15:37:47 GMT

Use Join:

TAB_SALDI:

Load * Inline

[

CONTO_KEY,OWNER,SALDO

10001EUR,XYZ,10000

];

join

TAB_MOV:

Load * Inline

[

CONTO_KEY,OWNER,MOV

10001EUR,XYZ,200

10001EUR,XYZ,300

10001EUR,XYZ,500

];

regards,

R.MayilVahanan

Re: Join 1:n

andrew001 — Thu, 25 Oct 2012 15:42:23 GMT

thanks

but I've difficult then in the aggragation in the pivot table, because I can use SUM with MOV column, but with SALDO how do I have to do ?

thanks

Re: Join 1:n

MayilVahanan — Thu, 25 Oct 2012 15:51:00 GMT

Please,Can you say your requirement in detail? Sum(SALDO) is enough know for calculating the saldo value?

or if you like to calculate one time means, please use sum(distinct saldo)

Re: Join 1:n

andrew001 — Thu, 25 Oct 2012 15:55:20 GMT

I'd want to SUM (MOV) but I'd want to consider the only first SALDO by CONTO_KEY

And I'd like to aggregate by CONTO_KEY or CONTO_KEY+OWNER (by pivot)

thanks

TAB_SALDI:

Load * Inline

[

CONTO_KEY,OWNER,SALDO

10001EUR,XYZ,10000

10002EUR,XYZ,20000

];

join

TAB_MOV:

Load * Inline

[

CONTO_KEY,ID,OWNER,MOV

10001EUR,1,XYZ,200

10001EUR,2,XYZ,300

10001EUR,3,XYZ,500

10002EUR,1,XYZ,200

10002EUR,2,XYZ,300

10002EUR,3,XYZ,500

];

Re: Join 1:n

MayilVahanan — Thu, 25 Oct 2012 16:04:24 GMT

Please check the attached file.. is ur requirement?

Re: Join 1:n

andrew001 — Thu, 25 Oct 2012 16:17:30 GMT

I'd like to obtain this result, in the pivot table (or by script)

from this

CONTO_KEY	OWNER	ID	sum(MOV)	SALDO
10001EUR	XYZ	1	200	10000
10001EUR	XYZ	2	300	10000
10001EUR	XYZ	3	500	10000
10002EUR	XYZ	1	200	20000
10002EUR	XYZ	2	300	20000
10002EUR	XYZ	3	500	20000

to this:

CONTO_KEY	OWNER	ID	sum(MOV)	SALDO
10001EUR	XYZ	1	200	10000
10001EUR	XYZ	2	300	0
10001EUR	XYZ	3	500	0
10002EUR	XYZ	1	200	20000
10002EUR	XYZ	2	300	0
10002EUR	XYZ	3	500	0

Re: Join 1:n

MayilVahanan — Thu, 25 Oct 2012 17:03:57 GMT

Check the attached file..

Regards,

R.MayilVahanan

Re: Join 1:n

CELAMBARASAN — Thu, 25 Oct 2012 17:18:15 GMT

Mayil used If conditions in his expression

instead of If expression you can go with set analysis which is faster

Sum({<ID={1}>} SALDO)

Or can be done within script as

TAB_SALDI:

Load *,CONTO_KEY&'1' AS Key Inline

[

CONTO_KEY,OWNER,SALDO

10001EUR,XYZ,10000

10002EUR,XYZ,20000

];

join

TAB_MOV:

Load *,CONTO_KEY&ID AS Key Inline

[

CONTO_KEY,ID,OWNER,MOV

10001EUR,1,XYZ,200

10001EUR,2,XYZ,300

10001EUR,3,XYZ,500

10002EUR,1,XYZ,200

10002EUR,2,XYZ,300

10002EUR,3,XYZ,500

];

then simply use as Sum(SALDO) as expression.

Re: Join 1:n

andrew001 — Thu, 25 Oct 2012 17:57:12 GMT

Thank you

a last issue: if the KEY '1' doesn't exists, the first ID is 2, , is there another solution ?

thank you in advance

Re: Join 1:n

CELAMBARASAN — Thu, 25 Oct 2012 18:25:11 GMT

Then check with it

TAB_SALDI:

Load *,CONTO_KEY&'1' AS Key Inline

[

CONTO_KEY,OWNER,SALDO

10001EUR,XYZ,10000

10002EUR,XYZ,20000

];

join

LOAD *,if(Previous(CONTO_KEY)<>Previous(CONTO_KEY),CONTO_KEY&'1',CONTO_KEY&ID) AS Key;

Load * Inline

[

CONTO_KEY,ID,OWNER,MOV

10001EUR,1,XYZ,200

10001EUR,2,XYZ,300

10001EUR,3,XYZ,500

10002EUR,1,XYZ,200

10002EUR,2,XYZ,300

10002EUR,3,XYZ,500

];

Note: conto_key field must be in a order