7 Replies Latest reply: Jul 28, 2011 8:45 AM by Sunil Chauhan RSS

    JOIN / Concatenate 2 tables: Help

    Byron Van Wyk

      Hi All,

       

      I have the following script...


      CrossTableDataNC:
      CrossTable(WkEndDate, PayValue, 7)
      LOAD ClientName,
      TempNo,
      TempName,
      CategoryDesc,
      Employer,
      Year,
      Invoice,
      [40628],
      [40635],
      [40642],
      [40649]
      FROM
      [Data\FPT\FPT April 2011.xlsx]
      (
      ooxml, embedded labels, table is [Data - Normal Hours Cost]);


      CrossTableDataNH:
      CrossTable(WkEndDate, Hours, 7)
      LOAD ClientName,
      TempNo,
      TempName,
      CategoryDesc,
      Employer,
      Year,
      Invoice,
      [40628],
      [40635],
      [40642],
      [40649]
      FROM
      [Data\FPT\FPT April 2011.xlsx]
      (
      ooxml, embedded labels, table is [Data - Normal Hours Hours]);

      Esssentially the data is identical except table one has a field PayValue and table 2 has a field Hours. I want to join these tables but I cant seem to figure out how. Please help, thanks Byron

        • JOIN / Concatenate 2 tables: Help
          Liron Baram

          hei byron

          when you say i want to join the tables

          what you mean

          what are the fields indenticale in both tables

          or the join should be by all 8 indetical fields

            • JOIN / Concatenate 2 tables: Help
              Byron Van Wyk

              Hi Liron

               

              Thanks for your quick reply. ALL FIELDS are idendtical EXCEPT the ONE WHERE THE CROSSTABLE QUALIFIER IS MENTIONED i.e. PayValue and Hours. Each table has also exactly the same number of rows. I cant explain why I cannot sort this out with the data before I load it, but need to sort it out within QV script environment. The payvalue amount corresponds to the number of hours delivered


              I want to JOIN the 2 tables together or essentially add the field HOURS to the first table in another column

                • JOIN / Concatenate 2 tables: Help
                  Sunil Chauhan

                  take resident of two tables and join them.

                   

                   

                   

                  Regards

                  Sunil

                  • JOIN / Concatenate 2 tables: Help
                    Byron Van Wyk

                    To add, if I just load the tables it joins perfectly but creates an unnecessary syn key table. I want it to join but to not create this syn key

                      • JOIN / Concatenate 2 tables: Help
                        Sunil Chauhan

                        take resident of two tables

                        ualify two tables

                        un qualify field required to join

                         

                         

                        Join Them

                         

                         

                         

                         

                        Regards

                        Sunil

                          • JOIN / Concatenate 2 tables: Help
                            Byron Van Wyk

                            Thanks Sunil. Suprisingly enough i tried something similar and it didnt work, this time round it did. Would you mind changing my script below to incorporate what you mean by qualify and unqualify. Thanks very much to everyones help


                            CrossTableDataNC:
                            CrossTable(WkEndDate, PayValue, 7)
                            LOAD ClientName,
                            TempNo,
                            TempName,
                            CategoryDesc,
                            Employer,
                            Year,
                            Invoice,
                            [40628],
                            [40635],
                            [40642],
                            [40649]
                            FROM
                            [Data\FPT\FPT April 2011.xlsx]
                            (
                            ooxml, embedded labels, table is [Data - Normal Hours Cost]);

                            CrossTableDataNCF:
                            LOAD *,
                            1
                            as Dummy
                            Resident CrossTableDataNC;
                            Drop Table CrossTableDataNC;

                            CrossTableDataNH:
                            CrossTable(WkEndDate, Hours, 7)
                            LOAD ClientName,
                            TempNo,
                            TempName,
                            CategoryDesc,
                            Employer,
                            Year,
                            Invoice,
                            [40628],
                            [40635],
                            [40642],
                            [40649]
                            FROM
                            [Data\FPT\FPT April 2011.xlsx]
                            (
                            ooxml, embedded labels, table is [Data - Normal Hours Hours]);

                            JOIN (CrossTableDataNCF)
                            LOAD *,
                            1
                            as Dummy
                            Resident CrossTableDataNH;
                            Drop Table CrossTableDataNH;

                             

                              • JOIN / Concatenate 2 tables: Help
                                Sunil Chauhan

                                CrossTableDataNC:
                                CrossTable(WkEndDate, PayValue, 7)
                                LOAD ClientName,
                                TempNo,
                                TempName,
                                CategoryDesc,
                                Employer,
                                Year,
                                Invoice,
                                [40628],
                                [40635],
                                [40642],
                                [40649]
                                FROM
                                [Data\FPT\FPT April 2011.xlsx]
                                (ooxml, embedded labels, table is [Data - Normal Hours Cost]);

                                 

                                CrossTableDataNH:
                                CrossTable(WkEndDate, Hours, 7)
                                LOAD ClientName,
                                TempNo,
                                TempName,
                                CategoryDesc,
                                Employer,
                                Year,
                                Invoice,
                                [40628],
                                [40635],
                                [40642],
                                [40649]
                                FROM
                                [Data\FPT\FPT April 2011.xlsx]
                                (ooxml, embedded labels, table is [Data - Normal Hours Hours]);


                                Qualify *;
                                Unqulify Fieldnamerequiredtojoin;
                                CrossTableDataNCF:
                                LOAD *,
                                1 as Dummy
                                Resident CrossTableDataNC;
                                Drop Table CrossTableDataNC;


                                JOIN (CrossTableDataNCF)
                                LOAD *,
                                1 as Dummy
                                Resident CrossTableDataNH;
                                Drop Table CrossTableDataNH;

                                 

                                 

                                 

                                Regards

                                Sunil Chauhan