Skip to main content
Announcements
Live today at 11 AM ET. Get your questions about Qlik Connect answered, or just listen in. SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
GabrielOtet
Contributor III
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
Partner - Specialist III
Partner - Specialist III

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 ....;

 

View solution in original post

2 Replies
marcus_malinow
Partner - Specialist III
Partner - Specialist III

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

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?