similar tables dynamic load

[sai_12]

Hi all,

I have 3 tables with same schema

The table names are table1,secondtable,table3 (table names are different but limited in number)

Currently i am doing 

table2:

load from table 1

concatenate

load from second table etc

INSTEAD of using many concatenations in loadscript 

is there a way i can bring them together

Like 

vfilelist ='table1','secondtable','table3'

for var in vfilelist:

concatenate

load from $var

Thank you in advance

[MartW]

this depends.

you can use qlik's auto concatenate feature. tables will auto-concat if the tabel structure is the same.

for example

table_1:

ordernumber	orderamount	year
1	10	2022
2	20	2022

MYSecondTable:

ordernumber	orderamount	year
499	1000	2023
500	25	2023

if you then load the tables after each other then qlik will auto-concatenate this into 1 table with 3 fields and 4 rows

otherwise you can use a loop to achieve this:

// for each file this will concatenate to the table
let vLIB = ''; // location of the directory you want to use (note this can also be a lib://)

BaseTable:
load null as KEY autogenerate(0);

for each t in FileList('$(vLIB)*.qvd')
    concatenate(BaseTable)
    load * from [$(t)] (qvd);
next

 this script will go to a directory and read in all qvd files there and concatenate them to the BaseTable

hope this helps

[sai_12]

Hi mart

Thank you for response

all the tables have similar format

ex:

table 1
colum1	column2

secondtable
colum1	column2

table3
colum1	column2

i think i cannot use filelist because the directory is full with other files but i only want to concatenate certain file

i am thinking of creating a variable list and somehow dynamically change the path in loadscript instead of writing multiple concatenation statements because in future if a new file comes i can just add name to list instead of changing the code

[rwunderlich]

You just about had it.

Set vfilelist ='table1','secondtable','table3';
for each var in vfilelist
  MyTable:
  load *  from [lib://mydata/$(var).csv] (...);
Next var

-Rob
http://www.easyqlik.com
http://masterssummit.com
http://qlikviewcookbook.com

[sai_12]

can vfilelist be list of variable names declared befoe, i tried this but i am getting error

Let vPayCurrent = year(today())&year(today())+1; 
Let vPayLastYear =year(today())-1&year(today()); 
Let vPay2YrsAgo = year(today())-2&year(today());

i am now using

Set vfilelist ='vPayCurrent','vPayLastYear','vPay2YrsAgo';
for each var in vfilelist

 MyTable:
  load *  from [lib://mydata/$(var).csv] (...);
Next var

This is giving error

[rwunderlich]

What error? Can you post your script? Note that my example

from [lib://mydata/$(var).csv] (...);

was just a sample. You need to adjust it for your actual connection (lib), filenames, and type.  TYou can do that by generating a load for any of the files using the wizard and then modifying. 

-Rob