Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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 ....;
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 ....;
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?