Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
There is a field shared by several tables for which I would like to apply a transformation.
For instance, lets consider a case with three tables, I would like to keep only the part after the last '/' for the field TrField and keep it with that name if possible. To that end I would use SubField(TrField, '/' ,-1) AS TrField.
However, I cannot do that operation before loading the data as I don't know the number of tables and their variables in advance.
Is there a way not involving several for loops and table copies to apply a transformation on a Field over different tables.
Source1:
LOAD
*
INLINE
[
TrField,Value1
Path\T1,1
A\Path\T1,1
A\Path\T2,1
];
Source2:
LOAD
*
INLINE
[
TrField,Value2
Path\T1,2
B\Path\T1,2
A\Path\T2,2
A\Path\T4,2
];
Source3:
LOAD
*
INLINE
[
TrField,Value3
Path\T1,3
A\Path\T1,3
A\Path\T2,3
A\Path\T4,3
A\Path\T5,3
];
Hi,
May be like this
LOAD
Value1 as Value,
SubField(TrField, '\',SubStringCount(TrField, '\')+1) as TrField;
Source1:
LOAD
*
INLINE
[
TrField,Value1
Path\T1,1
A\Path\T1,1
A\Path\T2,1
];
LOAD
Value2 as Value,
SubField(TrField, '\',SubStringCount(TrField, '\')+1) as TrField;
Source2:
LOAD
*
INLINE
[
TrField,Value2
Path\T1,2
B\Path\T1,2
A\Path\T2,2
A\Path\T4,2
];
LOAD
Value3 as Value,
SubField(TrField, '\',SubStringCount(TrField, '\')+1) as TrField;
Source3:
LOAD
*
INLINE
[
TrField,Value3
Path\T1,3
A\Path\T1,3
A\Path\T2,3
A\Path\T4,3
A\Path\T5,3
];
Hi Jon,
With the help of subfield, you can achieve this.
Thanks,
Arvind Patil
The prior load would work for transforming the variable my subfield command does work too.
The problem is that I am getting a variable number of tables from a folder and they may not have the TrField.
To clarify my question a bit, I have this files structure:
folder -
- Source_1 (file containing TrField)
- Source_2 (file not containing TrField)
- ...
- Source_N (file not containing TrField)
I cannot know how big is N neither if TrField is contained in Source_i
If file not containing TrField. What is the peculiarity of loading this table in view of the absence of field TrField?
Sorry for the delay in my response.
I load several files (tables) from the same folder. Some of them contain the TrField and in that case I want to transform them. Some other files (tables) in the same table do not contain that field.
The folder is load together and I cannot know the presence or absence of the field in advance. Due to that, if I use a preceding load I will generate a Field not found error.
Is there a simple way to transform the TrField when needed and avoid the Field not found error for the files without the TrField?
I assume there is 2 file structures - with and without TrField. Maybe You can load it like tjis:
LOAD *
FROM [lib://LIB/A\FILE*.xlsx]
(ooxml, embedded labels, table is Sheet1);
Files with the same structure will be concatenated into one table, and other structure into second one. Now drop the second and apply transformations