Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Reformat field contained in several tables

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

];

6 Replies
ahaahaaha
Partner - Master
Partner - Master

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

];

arvind_patil
Partner - Specialist III
Partner - Specialist III

Hi Jon,

With the help of subfield, you can achieve this.

Thanks,

Arvind Patil

Not applicable
Author

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


ahaahaaha
Partner - Master
Partner - Master

If file not containing TrField. What is the peculiarity of loading this table in view of the absence of field TrField?

Not applicable
Author

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?

tomasz_tru
Specialist
Specialist

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