topic help to find best join scenario in Qlik Learning Discussions

help to find best join scenario

Tue, 09 Dec 2014 05:41:42 GMT

Hello!

Could someone help me to find the best way of joining to tables?=)

I've got two tables, like

[A,B,C,D,E,F

1,2,3,4,5,6

5,4,1,2,3,8

4,1,2,7,8,9

7,8,9,6,5,4

];

[A,B,C,D,E,F

1,2,3,4,5,p

5,4,1,2,w,e

4,1,2,7,8,9

7,8,9,6,5,4

];

and as a result I need

1,2,3,4,5,6

5,4,1,2,3,8

5,4,1,2,w,e

4,1,2,7,8,9

7,8,9,6,5,4

So if only one last field differs, Qlik should take the value from first table, and if two fields differ, it should take BOTH lines to teh resulting table. practically my second table is a result of group- i'm calculating totals and if there was no total in the first table, I need to put it there, but if there already was then its in priority

Thanks in advance!

Re: help to find best join scenario

sushil353 — Tue, 09 Dec 2014 05:55:36 GMT

Hi,

Attached is solution code for your problem.

HTRH

Sushil

Re: help to find best join scenario

Tue, 09 Dec 2014 08:30:24 GMT

@Sushil Kumar, thank you for your answer!!

could you, please, advice me, how to combine it with group dy, made in load of 2nd table? 'cos I'm receivind invalid expression error, as expected...

Actual script is as follows:

Sales_plan:

load

SalesOffice,

State ,

Channel,

Production_Direction ,

TradeMark,

Product_Type ,

Amount,

Year,

Month,

if(len(trim(Group))=0,'All '&Type,Product_Type) as Product_Group,//if its an aggregated plan already

from sales.qvd (qvd);

Sales:

load

SalesOffice,

State ,

Channel,

Production_Direction ,

TradeMark,

Product_Type ,

Amount,

Year,

Month,

sum(Amount) as Amount,

'All '&Type as Product_Group

resident Sales_plan Group by Direction,TradeMark,Type,SalesIffice, State,Channel;