topic Table joining in QlikView

Table joining

Thu, 24 Feb 2011 12:00:35 GMT

Hi all,

how would i join these 4 tables in qlickviw scrirpt?

select ..... from a
left outer join b
left outer join c
left outer join d
on a.racct= b.saknr and
on a.racct = c.saknr and
on c.ktoks = d.ktoks

Table joining

maneshkhottcpl — Thu, 24 Feb 2011 12:14:15 GMT

Hi,

U can use

select * from A;

left join

Select * from B;

that

just one column name must same for link.

Table joining

Fri, 25 Feb 2011 03:36:32 GMT

sorry

i still cant get it.. to make it clear,my prob is highlighted as underlined and bold below:

how do i link this 2 field.

[GLT0]: // G/L account master record transaction figures
Load
[RRCTY] as [Record Type_RRCTY],
[RVERS] as [Version_RVERS],
[RYEAR] as [Fiscal Year_RYEAR],
[DRCRK] as [Debit/Credit Ind._DRCRK],
[RACCT] as key,
[RBUSA] as [Business Area_RBUSA],
[TSL01] as [Transaction currency_TSL01],
[TSL02] as [Transaction currency_TSL02],
[TSL03] as [Transaction currency_TSL03],
[TSL04] as [Transaction currency_TSL04],
[TSL05] as [Transaction currency_TSL05],
[TSL06] as [Transaction currency_TSL06],
[TSL07] as [Transaction currency_TSL07],
[TSL08] as [Transaction currency_TSL08],
[TSL09] as [Transaction currency_TSL09],
[TSL10] as [Transaction currency_TSL10],
[TSL11] as [Transaction currency_TSL11],
[TSL12] as [Transaction currency_TSL12],
[TSL13] as [Transaction currency_TSL13],
[TSL14] as [Transaction currency_TSL14],
[TSL15] as [Transaction currency_TSL15],
[TSL16] as [Transaction currency_TSL16];
SQL Select RRCTY RVERS RYEAR DRCRK RACCT TSL01 TSL02 TSL03 TSL04 TSL05 TSL06 TSL07 TSL08 TSL09 TSL10 TSL11 TSL12 TSL13 TSL14 TSL15 TSL16 RBUSA from GLT0

;
STORE * FROM [GLT0] INTO GLT0.QVD;

LEFT JOIN (GLT0)
LOAD
[SAKNR] as KEY,
[TXT20] as [Short Text_TXT20],
[TXT50] as [G/L Acct Long Text_TXT50];
SQL Select SAKNR TXT50 TXT20 from SKAT

;

LEFT JOIN (GLT0)
LOAD
[KTOKS] AS [G/L Account Group],
[SAKNR] as KEY;
SQL Select KTOKS SAKNR from SKA1

;

LEFT JOIN (SKA1)
LOAD
[KTOKS] AS [G/L Account Group],
[TXT30] as [Account Group Name];
SQL Select KTOKS TXT30 from SKA1

;

Table joining

deepakk — Fri, 25 Feb 2011 06:07:36 GMT

hi,

below is the code

GLT0]: // G/L account master record transaction figures
Load
[RRCTY] as [Record Type_RRCTY],
[RVERS] as [Version_RVERS],
[RYEAR] as [Fiscal Year_RYEAR],
[DRCRK] as [Debit/Credit Ind._DRCRK],
[RACCT] as key,
[RBUSA] as [Business Area_RBUSA],
[TSL01] as [Transaction currency_TSL01],
[TSL02] as [Transaction currency_TSL02],
[TSL03] as [Transaction currency_TSL03],
[TSL04] as [Transaction currency_TSL04],
[TSL05] as [Transaction currency_TSL05],
[TSL06] as [Transaction currency_TSL06],
[TSL07] as [Transaction currency_TSL07],
[TSL08] as [Transaction currency_TSL08],
[TSL09] as [Transaction currency_TSL09],
[TSL10] as [Transaction currency_TSL10],
[TSL11] as [Transaction currency_TSL11],
[TSL12] as [Transaction currency_TSL12],
[TSL13] as [Transaction currency_TSL13],
[TSL14] as [Transaction currency_TSL14],
[TSL15] as [Transaction currency_TSL15],
[TSL16] as [Transaction currency_TSL16];
SQL Select RRCTY RVERS RYEAR DRCRK RACCT TSL01 TSL02 TSL03 TSL04 TSL05 TSL06 TSL07 TSL08 TSL09 TSL10 TSL11 TSL12 TSL13 TSL14 TSL15 TSL16 RBUSA from GLT0

;
// STORE * FROM [GLT0] INTO GLT0.QVD; dont store it in qvdl.

LEFT JOIN (GLT0)
LOAD
[SAKNR] as KEY,
[TXT20] as [Short Text_TXT20],
[TXT50] as [G/L Acct Long Text_TXT50];
SQL Select SAKNR TXT50 TXT20 from SKAT

;

LEFT JOIN (GLT0)
LOAD
[KTOKS] AS [G/L Account Group],
[SAKNR] as KEY;
SQL Select KTOKS SAKNR from SKA1

LEFT JOIN

LOAD
[KTOKS] AS [G/L Account Group],
[TXT30] as [Account Group Name];
SQL Select KTOKS TXT30 from SKA1

;

store GLT0 into GLT0.qvd;

Table joining

Fri, 25 Feb 2011 08:07:42 GMT

Hi Deepak,

i'm sorry..this is there is a mistake.This is the right one.

last table should be T0772, not SKA1.

So there are 4 different tables. and my prob is to link SKA1 and T077Z (3rd and 4th tables) where SKA1.KTOKS= T077Z.KTOKS

[GLT0]: // G/L account master record transaction figures
Load
[RRCTY] as [Record Type_RRCTY],
[RVERS] as [Version_RVERS],
[RYEAR] as [Fiscal Year_RYEAR],
[DRCRK] as [Debit/Credit Ind._DRCRK],
[RACCT] as KEY,
[RBUSA] as [Business Area_RBUSA],
[TSL01] as [Transaction currency_TSL01],
[TSL02] as [Transaction currency_TSL02],
[TSL03] as [Transaction currency_TSL03],
[TSL04] as [Transaction currency_TSL04],
[TSL05] as [Transaction currency_TSL05],
[TSL06] as [Transaction currency_TSL06],
[TSL07] as [Transaction currency_TSL07],
[TSL08] as [Transaction currency_TSL08],
[TSL09] as [Transaction currency_TSL09],
[TSL10] as [Transaction currency_TSL10],
[TSL11] as [Transaction currency_TSL11],
[TSL12] as [Transaction currency_TSL12],
[TSL13] as [Transaction currency_TSL13],
[TSL14] as [Transaction currency_TSL14],
[TSL15] as [Transaction currency_TSL15],
[TSL16] as [Transaction currency_TSL16];
SQL Select RRCTY RVERS RYEAR DRCRK RACCT TSL01 TSL02 TSL03 TSL04 TSL05 TSL06 TSL07 TSL08 TSL09 TSL10 TSL11 TSL12 TSL13 TSL14 TSL15 TSL16 RBUSA from GLT0

;
STORE * FROM [GLT0] INTO GLT0.QVD;

LEFT JOIN (GLT0)
LOAD
[SAKNR] as KEY,
[TXT20] as [Short Text_TXT20],
[TXT50] as [G/L Acct Long Text_TXT50];
SQL Select SAKNR TXT50 TXT20 from SKAT

;

LEFT JOIN (GLT0)
LOAD
[KTOKS] AS [G/L Account Group],
[SAKNR] as KEY;
SQL Select KTOKS SAKNR from SKA1

;

LEFT JOIN (SKA1)
LOAD
[KTOKS] AS [G/L Account Group],
[TXT30] as [Account Group Name];
SQL Select KTOKS TXT30 from T077Z

;

Table joining

deepakk — Fri, 25 Feb 2011 08:17:18 GMT

hi,

GLT0]: // G/L account master record transaction figures
Load
[RRCTY] as [Record Type_RRCTY],
[RVERS] as [Version_RVERS],
[RYEAR] as [Fiscal Year_RYEAR],
[DRCRK] as [Debit/Credit Ind._DRCRK],
[RACCT] as KEY,
[RBUSA] as [Business Area_RBUSA],
[TSL01] as [Transaction currency_TSL01],
[TSL02] as [Transaction currency_TSL02],
[TSL03] as [Transaction currency_TSL03],
[TSL04] as [Transaction currency_TSL04],
[TSL05] as [Transaction currency_TSL05],
[TSL06] as [Transaction currency_TSL06],
[TSL07] as [Transaction currency_TSL07],
[TSL08] as [Transaction currency_TSL08],
[TSL09] as [Transaction currency_TSL09],
[TSL10] as [Transaction currency_TSL10],
[TSL11] as [Transaction currency_TSL11],
[TSL12] as [Transaction currency_TSL12],
[TSL13] as [Transaction currency_TSL13],
[TSL14] as [Transaction currency_TSL14],
[TSL15] as [Transaction currency_TSL15],
[TSL16] as [Transaction currency_TSL16];
SQL Select RRCTY RVERS RYEAR DRCRK RACCT TSL01 TSL02 TSL03 TSL04 TSL05 TSL06 TSL07 TSL08 TSL09 TSL10 TSL11 TSL12 TSL13 TSL14 TSL15 TSL16 RBUSA from GLT0

LEFT JOIN (GLT0)
LOAD
[SAKNR] as KEY,
[TXT20] as [Short Text_TXT20],
[TXT50] as [G/L Acct Long Text_TXT50];
SQL Select SAKNR TXT50 TXT20 from SKAT

;

LEFT JOIN

LOAD
[KTOKS] AS [G/L Account Group],
[SAKNR] as KEY;
SQL Select KTOKS SAKNR from SKA1

;

LEFT JOIN

LOAD
[KTOKS] AS [G/L Account Group],
[TXT30] as [Account Group Name];
SQL Select KTOKS TXT30 from T077Z

Store GLT0 into GLT0.qvd;

The thrid and the fourth table will link based on th [G/L Account Group] field.

Table joining

Fri, 25 Feb 2011 08:35:50 GMT

HI deepak,

I did used your script but it doesnt works

after reload the table, there is still no [TXT30] as [Account Group Name] data from T077Z (4th table).

Table joining

Fri, 25 Feb 2011 08:47:07 GMT

i already got it right deepak..thank you very much..