7 Replies Latest reply: Feb 25, 2011 3:47 AM by Zuleinie Bt. Zainal Abidin RSS

    Table joining

       

      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
          Manesh KHOT

          Hi,

          U can use

          select * from A;

          left join

          Select * from B;

           

          like

          that

          just one column name must same for link.

            • Table joining

              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
                  Deepak Kurup

                  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

                      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
                          Deepak Kurup

                          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.