Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
GabrielOtet
New Contributor III

Adding a field from already calculated fields

Hi,

It seems simple but I cannot find a way to implement it:

I have a table which is loaded from a database. To that table, I add (when loading) another 5 columns based on some calculations.

In addition, I have to sum up the data from already calculated 5 columns into another column (6th one) but seems to not be possible while loading the table as the newly created columns are not recognized yet.

Anyhow, this would not be a big problem as I suppose I can add an additional column to the table after it was loaded. The problem is that I have no idea how to do it.

Loading function is something like below (simplified, with fewer columns):

LOAD Field1,

Field2,

Field3, 

Field4,

..................

// 1st additional column
IF (Field1 <> NULL() OR Field2 <> NULL(), 'No',
IF(WildMatch(Field3, '1801*', '2850*', '1300*', '3050*', '1600*', '3350*'), '20',
IF(WildMatch(Field3, '6300*', '101*', '6400*', '6200*', '227*'), '10',
IF(WildMatch(Field3, '125*', '3749*'), '5', '0')))) as Additional_Field1,

// 2nd additional column
IF (Field1 <> NULL() OR Field2 <> NULL(), 'No',
IF(WildMatch(Field4, '1801*', '2850*', '1300*', '3050*', '1600*', '3350*'), '20',
IF(WildMatch(Field4, '6300*', '101*', '6400*', '6200*', '227*'), '10',
IF(WildMatch(Field4, '125*', '3749*'), '5', '0')))) as Additional_Field2,
..........................
..........................
SQL SELECT 

Field1,

.......


FROM ....;

To this table, I have to add Additional_Field6 as a sum of (Additional_Field1+Additional_Field2+...)

Does someone know how to do it?

1 Solution

Accepted Solutions
marcus_malinow
Valued Contributor III

Re: Adding a field from already calculated fields

A preceding load should do the trick:

 

LOAD

 *,

Additional_Field1+Additional_Field2+... as Additional_Field6

;

LOAD Field1,

Field2,

Field3, 

Field4,

..................

// 1st additional column
IF (Field1 <> NULL() OR Field2 <> NULL(), 'No',
IF(WildMatch(Field3, '1801*', '2850*', '1300*', '3050*', '1600*', '3350*'), '20',
IF(WildMatch(Field3, '6300*', '101*', '6400*', '6200*', '227*'), '10',
IF(WildMatch(Field3, '125*', '3749*'), '5', '0')))) as Additional_Field1,

// 2nd additional column
IF (Field1 <> NULL() OR Field2 <> NULL(), 'No',
IF(WildMatch(Field4, '1801*', '2850*', '1300*', '3050*', '1600*', '3350*'), '20',
IF(WildMatch(Field4, '6300*', '101*', '6400*', '6200*', '227*'), '10',
IF(WildMatch(Field4, '125*', '3749*'), '5', '0')))) as Additional_Field2,
..........................
..........................
SQL SELECT 

Field1,

.......


FROM ....;

 

2 Replies
marcus_malinow
Valued Contributor III

Re: Adding a field from already calculated fields

A preceding load should do the trick:

 

LOAD

 *,

Additional_Field1+Additional_Field2+... as Additional_Field6

;

LOAD Field1,

Field2,

Field3, 

Field4,

..................

// 1st additional column
IF (Field1 <> NULL() OR Field2 <> NULL(), 'No',
IF(WildMatch(Field3, '1801*', '2850*', '1300*', '3050*', '1600*', '3350*'), '20',
IF(WildMatch(Field3, '6300*', '101*', '6400*', '6200*', '227*'), '10',
IF(WildMatch(Field3, '125*', '3749*'), '5', '0')))) as Additional_Field1,

// 2nd additional column
IF (Field1 <> NULL() OR Field2 <> NULL(), 'No',
IF(WildMatch(Field4, '1801*', '2850*', '1300*', '3050*', '1600*', '3350*'), '20',
IF(WildMatch(Field4, '6300*', '101*', '6400*', '6200*', '227*'), '10',
IF(WildMatch(Field4, '125*', '3749*'), '5', '0')))) as Additional_Field2,
..........................
..........................
SQL SELECT 

Field1,

.......


FROM ....;

 

GabrielOtet
New Contributor III

Re: Adding a field from already calculated fields

It works 🙂

For me is very counter-intuitive that is working as first is loading a field based on other fields that do not exist yet. Do you know why is like this?