Solved: Join 1:n - Qlik Community

andrew001 · ‎2012-10-25

hi

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

CELAMBARASAN · ‎2012-10-25

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

View solution in original post

MayilVahanan · ‎2012-10-25

HI

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

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.

andrew001 · ‎2012-10-25

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

MayilVahanan · ‎2012-10-25

HI

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)

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.

andrew001 · ‎2012-10-25

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

];

MayilVahanan · ‎2012-10-25

HI

Please check the attached file.. is ur requirement?

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.

andrew001 · ‎2012-10-25

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

MayilVahanan · ‎2012-10-25

Hi

Check the attached file..

Regards,

R.MayilVahanan

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.

CELAMBARASAN · ‎2012-10-25

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.

andrew001 · ‎2012-10-25

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