Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all
I have a list of field names that I would like to store in a variable/ list and then used to load datasets from the server.
example
vlistofFieldNames = ('F1','F2','F3','F4','F5')
then to load my datasets. Example:
[Table1]:
SELECT vlistofFieldNames
FROM database_1
WHERE date = '2023-01-01';
[Table2]:
SELECT vlistofFieldNames
FROM database_2
WHERE date = '2023-01-01';
Is that possible and what would the syntax be like? Thanks in advance
It's possible and there are probably multiple ways to specify the creating and calling of the variable. Personally I use mostly;
set vlistofFieldNames = "text([F1]) as [Fx],[F2] as [Fy],[F3],[F4],[F5]";
[Table1]: load $(vlistofFieldNames) from ...
which means not only fieldnames else entire (load) statements could be specified. By loading from a data-base you need of course using their wrapping- and functions-syntax/portfolio.
Not in this sense because the variable here is a string and not an array or a list. This means it would need something like:
let var = replace('$(var)', '[F5]', '');
but depending on the position of the entry you may need to remove also a comma respectively by multiple entries and/or more complex logic some extra checks and handling.
An alternatively may be to load the field-list in a table, like:
t1: load * inline [
F
F1
F2 as F3
...
];
and then something:
t2: load concat(F, ',') as X resident t1;
let var = peek('X', 0, 't2');
The tables could be now regarded as array and not wanted fields/statements could be removed with a where-clause.
It's possible and there are probably multiple ways to specify the creating and calling of the variable. Personally I use mostly;
set vlistofFieldNames = "text([F1]) as [Fx],[F2] as [Fy],[F3],[F4],[F5]";
[Table1]: load $(vlistofFieldNames) from ...
which means not only fieldnames else entire (load) statements could be specified. By loading from a data-base you need of course using their wrapping- and functions-syntax/portfolio.
@marcus_sommer Thank you very much !
A follow up question, is there a way to remove a field/value in code from the variable?
Example I declared:
set vlistofFieldNames = "text([F1]) as [Fx],[F2] as [Fy],[F3],[F4],[F5]";
but since one or two datasets out of 100 does not have the field: F5,
can I do the python equivalent of : vlistofFieldNames. drop("F5")
resulting in my variable becoming vlistofFieldNames = "text([F1]) as [Fx],[F2] as [Fy],[F3],[F4]";
Not in this sense because the variable here is a string and not an array or a list. This means it would need something like:
let var = replace('$(var)', '[F5]', '');
but depending on the position of the entry you may need to remove also a comma respectively by multiple entries and/or more complex logic some extra checks and handling.
An alternatively may be to load the field-list in a table, like:
t1: load * inline [
F
F1
F2 as F3
...
];
and then something:
t2: load concat(F, ',') as X resident t1;
let var = peek('X', 0, 't2');
The tables could be now regarded as array and not wanted fields/statements could be removed with a where-clause.