looping Through Multiple data connections

[Ghadijah]

Hello Everyone, 

I am relevantly new to Qliksense and was hoping to get some guidance. 

I need to loop through multiple different data connections and establish the row count of each file/table within the connection. 

Is this possible on Qliksense and if it is does anyone have an example load script to do this ?

Thanks  

[Andrei_Cusnir · Accepted solution]

Hello,

If my understanding is correct, then you have some connections with data sources and you want to count the rows in every connection. Hypothetically, speaking this might not be 100% possible to achieve. Please allow me to explain why I believe that your specific use case scenario is not possible.

Data connection:

A data connection is essentially just a connection to the source where the data is connected. This can be either a folder with files (EXCELs, CSVs, QVDs etc.), a database such as (MySQL, PostgreSQL etc.) and other data sources. The connection it self, doesn't define the data within the connection so you can't just count the rows.

Loading data example:

LOAD
   Havan,
   MetaData
FROM [lib://test/Book2.xlsx]
(ooxml, embedded labels, table is DataSheet);

From the script above we are loading the fields Havan and MetaData from a connection with the data source so we can count how many rows of data are there. There are a couple of issues if you want to repeat this process for multiple connections. For example:

1. The fields might be different (For this table you want to count the rows for data Havan and MetaData but for another table there might not be these fields so you want to count other fields)
2. The connection part might be different "lib://test/" as it is a different connection, then the the path will be different
3. The file source where the data is found within the connection is different e.g. "Book2.xlsx"
4. The sheet might be different "DataSheet"
5. One connection might be referring to PostgreSQL database and another connection might be referring to 3 different CSV file within the folder connection.

It is almost impossible to write a script that will iterate through different connections, different types of connections, take into consideration how the data is loaded within each connection, find the right table names etc. Another example is:

As you can see the connection path is different, the sheet is different, the fields in each load are different, the file is different so there is no easy way of actually writing a script that would take all these differences into consideration and those connections all refer to folder connection type. So if you are about to introduce other types of connection, then this is going to get even more complicated.

The ideally environment where this could be possible is to have the following requirements:

1. All the connections have similar names:
   1. lib://con1/
   2. lib://con2/
   3. lib://con2/
   4. etc.
2. All the connections are the same type: Either all the connections are Folders or PostgreSQL etc.
3. All the fields are the same, like the data you load are exactly the same "Details" in all tables (In particular example above)
4. The sheet names are the same (In particular example above)
5. The files have the same names (In particular example above)

Then you can only make a For...Next statement that will execute the exact same script (with only the differences being the numbers in the files/connections names). 

I hope that this information was helpful. In case I have misunderstood the use case scenario, please elaborate in details by providing additional information. However, if it has helped you resolve the issue, addressed your concerns or at least pointed you in the right direction, please mark it as Accepted Solution to give further visibility to other community members.