Table editor vs Script

[Anonymous]

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!

[pablolabbe]

Check this documentation about  Best Practices for data modeling.

https://help.qlik.com/en-US/sense/September2017/Subsystems/Hub/Content/Scripting/combine-tables-join...

[PrashantSangle]

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,

[rwunderlich]

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

[Gabriel]

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

[Anonymous]

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:

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

[rwunderlich]

"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

[Gabriel]

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

[Anonymous]

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

[rwunderlich · Accepted solution]

Yes, I would agree with your approach.  Note that you can also build the initial load statements in the script editor as well.  There is no need to use the visual Data Manager unless there is some benefit to you.

-Rob

http://masterssummit.com

http://qlikviewcookbook.com