Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
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
Honored Contributor

Re: Reformat field contained in several tables

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
Valued Contributor III

Re: Reformat field contained in several tables

Hi Jon,

With the help of subfield, you can achieve this.

Thanks,

Arvind Patil

Not applicable

Re: Reformat field contained in several tables

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
Honored Contributor

Re: Reformat field contained in several tables

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

Not applicable

Re: Reformat field contained in several tables

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
Valued Contributor

Re: Reformat field contained in several tables

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