topic Re: Join problem in QlikView

Join problem

vikasgupta — Wed, 26 Dec 2012 09:28:48 GMT

Hi every one

I have two tables location master and Transaction table

/************Location master ********/

LocationMaster:

LOAD

LocCd,

LocCoCd,

LocDesc,

LocTyp

FROM

[..\QVD\LocationMaster.Qvd]

(qvd);

/************Transaction master ********/

Txnd:

LOAD

TdRmSz,

TdRmStkRt,

TdRmQty,

TdFrRmLoc,

TdToRmLoc,

TdFrRmDc

FROM

[..\QVD\Txnd.qvd]

(qvd);

here condition is that i want to join LocCd from location master to Transaction master on TdFrRmLoc and TdToRmLoc

means condition is that join loc on (LocCd=TdFrRmLoc or LocCd=TdtoRmLoc) to access Loctyp .

Thanks

Vikas

Re: Join problem

Wed, 26 Dec 2012 10:14:23 GMT

U can write a script like below.

LocationMaster:

LOAD

LocCd as Key,

LocCd,

LocCoCd,

LocDesc,

LocTyp

FROM

[..\QVD\LocationMaster.Qvd]

(qvd);

Inner join

LOAD

TdRmSz,

TdRmStkRt,

TdRmQty,

TdFrRmLoc as Key

TdFrRmLoc,

TdToRmLoc,

TdFrRmDc

FROM

[..\QVD\Txnd.qvd]

(qvd);

Concatenate

LocationMaster:

LOAD

LocCd as Key,

LocCd,

LocCoCd,

LocDesc,

LocTyp

FROM

[..\QVD\LocationMaster.Qvd]

(qvd);

Inner join

LOAD

TdRmSz,

TdRmStkRt,

TdRmQty,

TdToRmLoc as Key

TdFrRmLoc,

TdToRmLoc,

TdFrRmDc

FROM

[..\QVD\Txnd.qvd]

(qvd);

I hope , it helps u

~Kabilan K.

Re: Join problem

Gysbert_Wassenaar — Wed, 26 Dec 2012 10:27:02 GMT

I assume that TdFrRmLoc and TdtoRmLoc are the From- and To- locations. So they are two different things. That means you can't join both fields to one LocationMaster table directly. You can do several things.

Use a mapping table to add LocTyp (and perhaps LocDesc) to the Txnd table for both From- and To- locations.
Create two LocationMaster tables, one for From- locations and one for To- locations
Create a link table
Use LocationMaster as a data island and don't associate it with Txnd. Use field triggers make selections.

Option 2 is the easiest to do:

LocationsFrom:

LOAD

LocCd as TdFrRmLoc,

LocCoCd as LocFromCoCd,

LocDesc as LocFromDesc,

LocTyp as LocFromTyp

FROM

[..\QVD\LocationMaster.Qvd]

(qvd);

LocationsTo:

LOAD

LocCd as TdToRmLoc ,

LocCoCd as LocToCoCd,

LocDesc as LocToDesc,

LocTyp as LocToType

FROM

[..\QVD\LocationMaster.Qvd]

(qvd);

Option3 is fairly easy too and gives you a bit more flexibility:

LocationLink:

TdRmSz, //assuming this is the primary key of Txnd, if not replace it with the field that is

TdFrRmLoc as LocCd,

'From' as LocationGroup

resident Txnd;

Concatenate (LocationLink)

TdTmSz,

TdToRmLoc as LocCd,

'To' as LocationGroup

resident Txnd;

Re: Join problem

vikasgupta — Sat, 29 Dec 2012 06:32:39 GMT

Hi Gysbert

I used second option given by you and created two tables of location master i also tried third option but Txn table have millions of records so I did not use third option .

Now i am stuck with condition and dimension used for expression so i want to ask that which is more suitable for for following function .

My (SQL) function is following which i want to calculate :-

===============================================================

select * from

(select loccd,

sum(case when TdFrRmLoc=loccd and TdFrRmDc='c' and tdt between '01/06/05' and '30/08/05' then TdRmWt else 0 end)+

sum(case when TdToRmLoc=loccd and TdToRmDc='C' and tdt between '01/06/05' and '30/08/05' then TdRmWt else 0 end))

from txn join txnd

on ttc=tdtc and tyy=tdyy and tchr=tdchr and tno=tdno

join loc on (LocCd=TdFrRmLoc or LocCd=TdtoRmLoc) and LocTyp='S' and loccocd='DDL'

join RmMst on RmCd=TdRmCd

Where Rmctg IN ('d')

group by loccd)

=================================================================================

No. of table and its records

Txn:64195

Txnd: 2659256 Rows

Loc:64 Rows

Txn and Txnd already joind by Key .

Thanks

Vikas Gupta

Re: Join problem

vikasgupta — Thu, 03 Jan 2013 08:25:17 GMT

Hi Everyone

Can anyone help me about how to apply field Triggers for above condition means join Location master to Txnd table .I wants to join TdFrRmLoc and TdToRmLoc to location master for accessing LocTyp.

Thanks

Vikas