Load tables from sql

[paulwalker]

Experts!

I'm loading multiple tables at once using for loop and working fine,  below is the my script. 

SQL USE Test;

ListOfTables:

LOAD 

RowNo() AS SrNo,

TableName

FROM

[Tables.xlsx]

(ooxml, embedded labels, table is Sheet1);

Temp:

LOAD 

MAX(SrNo) AS NoOfTables

RESIDENT ListOfTables;

LET vNoOfTables= FieldValue('NoOfTables',1);

DROP TABLE Temp;

FOR i=1 to $(vNoOfTables)

LET vTable=FieldValue('TableName',$(i));

$(vTableName):

SQL SELECT * FROM $(vTable);

STORE [$(vTable)] INTO $(vTable).QVD (QVD);

DROP TABLE [$(vTable)];

NEXT i

but my users are asking to load only required columns, is there any possibility ? 

Thanks in Advance!

[MatheusC]

@paulwalker 

using SELECT with the * character.
This will return all existing fields in the SQL table.

Simply insert the required fields by removing the character.

SQL SELECT
FieldA,
FieldB,
FieldC
FROM $(vTable);

- Matheus

[paulwalker]

fields should be automated.. 

let say Category Table has 3 fields and Product table has 10 fields.

If I keep all the column names it will through an error.

[marcus_sommer]

Yes, it's possible to apply something like this:

$(vTableName):

SQL SELECT $(vFields) FROM $(vTable) where $(vMyConditions);

The information must only be loaded in some way and then assigned to the variables. In your case maybe just added within the table-list Excel, like:

Table   Fields   Conditions
A          F1, F2  Year >= 2024
B          F1, F3  1 = 1

but there are also other ways like providing the information as rows and using concat() aggregations to create the lists. Depending on the data-base and their syntax-rules it might need some efforts to differentiate between fields, functions, numbers and strings to apply the right quote/bracket-wrapping. Because of the fact that there are duplicates possible within the fields or conditions you should switch the query from fieldvalue() to peek().

[QFabian]

Hi @paulwalker , one thing is creating the QVDs, that usually store all the columns, and then users, or in a users App, you load just the required fields.

[paulwalker]

Yes, your correct.

but It is taking more time to load all the tables into QVD's (Many tables have description kind of data and QVD's size also it's huge like showing GB's).

Just this is my thought, if I create like below structure (Required column is like flag, 1 should be required column and 0 we don't require - in future if we want any column just change 0 to 1)

[marcus_sommer]

Such structure might be loaded per:

t: load Table, concat(Field, ',') as Fields from XLSX where Required = 1 group by Table;

for i = 0 to noofrows('t') - 1
   let t = peek('Table', $(i), 't');
   let f = peek('Fields', $(i), 't');
...

and if the data-set are really huge you may add like above hinted appropriate where-clauses and/or extending the logic by implementing an incremental approach, for example storing the max. date from each load in a variable and then only loading newer data and adding them to the historic ones. There are a lot of possibilities to reduce the workload of the data-base - of course by increasing the complexity ...