9 Replies Latest reply: Mar 15, 2018 11:23 AM by Rob Wunderlich RSS

    Table editor vs Script

    Gabriele Zelasco

      Hello, new to qliksense.

      I loaded and joined twelve Oracle tables through the table visual editor (no script).

      Everything has worked fine.

      Now I would to create a subset table selecting (or loading)  some fields from the twelve tables through script.

      I've read a little about LOAD syntax and I have a basilar question for you.

      I can refer to joined tables in a script ?

      What is the right syntax of the FROM clause referring to the joined tables ?

      ex. LOAD field1,field2,field3 FROM ?

      Is it possible to get a reference to the joined tables as a whole object ?

      Thank in advance!

        • Re: Table editor vs Script
          Prashant Sangle

          Hi,

           

          Can you explain your table structure which you want to join? also tell us which field should be joining field.

          We will help you with syntax.

           

          Basic syntax of joining to table is

           

          Load id,Name from table1;

          Left join

          Load id,Address from table2;

           

          In above example id is joining field. You can left / right/ outer whichever you prefer.

           

          Regards,

          • Re: Table editor vs Script
            Rob Wunderlich

            What is the right syntax of the FROM clause referring to the joined tables ?

            ex. LOAD field1,field2,field3 FROM ?

            Is it possible to get a reference to the joined tables as a whole object ?


            LOAD field1, field2, field3 Resident FactTable;


            where:

            Resident is the keyword that indicates your source is a table loaded previously in the script.

            FactTable (for example) is the name of that previously loaded table.


            Just make sure your added script comes after the auto generated section.


            -Rob

              • Re: Table editor vs Script
                Gabriele Zelasco

                Hello,

                I want to thank you all for the replies received. This is a very reactive and useful community.

                Let me explain a little better my question.

                I have already imported and associated many Oracle tables into Qliksense.

                I used the Qliksense visual table editor and I got the following structure:

                screenshot.1.jpg

                I have no problems at all with the table editor, it's simple and intuitive.

                I have no problems even with SQL language. I could obtain the same result

                by sql query (inner, left, right join, ecc.).

                I will try to summarized what I really don't understand (yet) in the following question :

                 

                How can I refer to the multiple tables imported in the script editor ?

                I don't need to refer to a single part of it.

                I want to make a load script referring to all the tables joined as an unique object.

                 

                Another little ex.

                3 Tables  joined through the visual table editor:

                T1 (id,name,surname)

                T2 (id,address,phone, email)

                T3 (id,datebirth,city,zip)

                joined by id field

                 

                Then in the script editor I want to get another table from that  :

                Load id, name, surname, email, datebirth  Resident (multiple tables joined not a single one) ?

                This is my lack

                 

                I hope there's no need to reproduce completely by script what the visual editor already does !

                Thank you again for the support and patience

                 

                Gabriel

                  • Re: Table editor vs Script
                    Rob Wunderlich

                    "How can I refer to the multiple tables imported in the script editor ?

                    I don't need to refer to a single part of it.

                    I want to make a load script referring to all the tables joined as an unique object."

                     

                    "Load id, name, surname, email, datebirth  Resident (multiple tables joined not a single one) ?"

                     

                    You cannot.  When reading a table in the script, you can only refer to a single table.  It is different than the chart view, where the entire data set appears as a single joined table.

                     

                    There are some methods you can use to indirectly do what you want.

                    1. You can script join the tables together into a temp table and Load Resident from the Temp.

                    2. You can use Mapping Tables and the ApplyMap() function to "reach" other tables in the Load.

                    3. You can use the Lookup() function to "reach" other tables. Lookup() can be quite slow, ApplyMap() is typically much faster.

                     

                    -Rob

                    • Re: Table editor vs Script
                      Gabriel Oluwaseye

                      To further add to what Rob said. Instead of having multiple tables, aim to have 1 table you can finally refer to. I.e.

                      your T1, you can create Mapping table from T2 using APPLYMAP to bring address, phone,email fields into T1, Meaning if you want to refer to T1 for another purpose it's much easier than 2 or more tables.

                      Further reading on MAPPING & APPLYMAP will serve you well in the future.

                       

                      Gabriel

                    • Re: Table editor vs Script
                      Gabriele Zelasco

                      Good morning,

                      many thank Rob and Gabriel!

                       

                      I would like to submit you just a final thought and receive your kind and wise contribution.

                      I could just import, through the visual table editor, all the tables I need from db without associating them (just single tables loaded in memory).

                      After that I could do all the rest from script editor. With a single LOAD script I could obtain a single large table, joining the existing tables and obtaining the fields I need.

                      I find the script management side much more flexible. Also in the script I can filter the data with WHERE clause. This can not be done in the visual table editor.

                      Is this a correct approach adhering to best practices?

                      Thanks again to all those who helped me

                    • Re: Table editor vs Script
                      Gabriel Oluwaseye

                      Hi,

                       

                      Just little addition to Rob's point.

                      When you use RESIDENT to call a table in memory, if you don't use NOCONCATENATE clause your new table will disappear.

                       

                      Example:

                      tmp0:

                      LOAD fld1,fld2,fld3 FROM tb1

                      LEFT JOIN (tmp0)

                      LOAD fld1, fld4, fld 10 FROM tb2;

                       

                      NOCONCATENATE   // without this FinalTable will append to  tmp0

                       

                      FinalTable:

                      LOAD  * RESIDENT tmp0;

                       

                      DROP TABLE tmp0;  // optional if tmp0 table is not needed.

                       

                      hope this make sense in addition to Rob's point.

                       

                       

                      Gab