5 Replies Latest reply: Dec 27, 2013 7:20 AM by cl bld RSS

    Select fields from existing table

      All

       

      I would like to join tables that are already in QLikView document.

       

      Here is how i initially load these tables:

       

      LOAD Portfolio,

           [Rev 2013],

           [Rev 2012],

           [Rev 2011]

      FROM

      [F:\MYPATH\RevenueYTD.xls]

      (biff, embedded labels, table is [Revenue by Portfolio$]);

       

      now i am writing script to left join the (List) Tables Portfolio and [Rev 2013]. The resulting table i want be called RevenueByPortfolio.

       

      RevenueByPortfolio:

      Left Join (Portfolio) Load * Resident [Revenue 2013];

       

      I get error Table not Found.

       

      Should I use Resident keyword twice in expression, and how ? or frrom_field ?

       

      Regards

        • Re: Select fields from existing table
          Tresesco B

          A generic example:

           

          TableA:

          Load A,

                    B,

                    C

          From <>;

           

          Left Join (TableA)   // table name and NOT field name

          TabgetTableName:

          Load A   // 'A' is common in bothe the tables, so the join would be on 'A'.

                    X,  //Note, Multiple common fields would create a composite key

                    Y

          Resident <table name>;

           

          Hope this helps.

          • Re: Select fields from existing table
            Gysbert Wassenaar

            You seem to be confusing fields with tables.

            LOAD Portfolio,

                 [Rev 2013],

                 [Rev 2012],

                 [Rev 2011]

            FROM

            [F:\MYPATH\RevenueYTD.xls]

            (biff, embedded labels, table is [Revenue by Portfolio$]);

            This load statement only loads one table, not multiple tables. That one table has four fields. The names of the fields are Portfolio, [Rev 2013], [Rev 2012] and [Rev 2011]. You cannot join fields. The fields are already part of one table.


            Add a table box object to your qlikview document and add all four fields. You'll see one table with four fields. You can also look at the internal data model of your document using the table viewer. That will show you the tables that are loaded and associations between any tables.

              • Re: Select fields from existing table

                Thanks, what you say does make sense.

                 

                But fields Rev 2012, Rev 2013, Rev 2011 are also present in other tables I have loaded. When I do as you describe a Table Box with the four fields, I have the first lines that have all fields filled with values. the last lines have no portfolio field value, but other columns are non empty. with a left join, i wanted solve this issue having only lines that have a portfolio value.

                 

                Regards

                  • Re: Select fields from existing table
                    Gysbert Wassenaar

                    Have you read Joins and Lookups? It explains how to join tables. I think you may be trying to specify which fields should be used for the join like is done in sql statements. In Qlikview you only specify the table it should be joined with, not the fields. The join will be made using fields with the same names in both tables.

                      • Re: Select fields from existing table

                        Thanks for the link.

                         

                        Then, SELECT would do for selecting precise fields from one table. I found out the problem with my document is that the table view's relationships are far from those of the original document (that i sent to Excel table by table, then loaded by with a script in QLikView).

                         

                        How can i, literaly, copy one QLikView sheet, say, to Excel, and load it back in QLikView on a blank sheet, while preserving the logic in the Table View ? What i am doing from now is sending to Excel all tables from original document one by one, and loading them back using the Load wizard in script editor. All field are loaded into separate List Tables, and I try to build back the tables as in the original document. But as Table View is not at all replicated as in the original document, the tables i obtain with right click -> new sheet object are far from the original ones.

                         

                        Do you have an idea?

                         

                        Regards