Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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
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
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