Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I want to concatenate two tables using below conditions and create a new column while concatenating:
table 1 - when region is null concatenate table 2 - when region is not null and district is null
Add new column XYZ with all values as - XYZ Add new column - Region with values that of region column
Replace "Null" value of region column by "All" Replace region column values by value "All"
Set NullInterpret = 'Null';
LOAD 'ALL' as Region,'ALL' as District, Value,
If(len(trim(Region))=0, 'ALL',If(Len(Trim(District))=0,Region,'Territory')) as NewRegion;
LOAD * INLINE [
Region District Value
Null Null 19
Null Null 5
Null Null 8
Null Null 11
Region 1 Null 1
Region 2 Null 5
Region 3 District 1 10
] (delimiter is '\t');
I hope you see how you can check for NULL in the different input fields and then create a logic upon this.
Could you post two small sample input tables and your expected result?
Table 1 | ||
Region | District | Value |
Null | Null | 19 |
Null | Null | 5 |
Null | Null | 8 |
Null | Null | 11 |
Region 1 | null | 1 |
Region 2 | null | 5 |
Condition is somewhat like this "where region is null OR (Region is not null and district is null)" add new column and replace region and district both null with value 'All' and if region is not null then add that value in new column
Output
Output | |||
---|---|---|---|
Region | District | Value | New Column |
Null | Null | 19 | All |
Null | Null | 5 | All |
Null | Null | 8 | All |
Null | Null | 11 | All |
Region 1 | null | 1 | Region 1 |
Region 2 | null | 5 | Region 2 |
try this
LOAD *,
if(len(trim(Region))=0 and len(trim(District))=0 ,'All',
if(not isnull(Region),Region)) as NewColumn
...
FROM atble
Something like
Set NullInterpret = 'Null';
LOAD *,
If(len(trim(Region))=0, 'ALL',Region) as NewRegion;
LOAD * INLINE [
Region District Value
Null Null 19
Null Null 5
Null Null 8
Null Null 11
Region 1 Null 1
Region 2 Null 5
] (delimiter is '\t');
I haven't full got your second condition:
OR (Region is not null and district is null)" add new column and replace region and district both null with value 'All'
your result table seems not to demonstrate this
The above will add new column and add value 'All' in the column, but when value is not null in region column and in district column value is null then I want regions to be picked e.g.,
Region District
Region 1 Null
Region 2 District 1
Null Null
Null Null
Null Null
Output should be
Region District New Column
All All Region 1
All All Region 2
All All All
All All All
All All All
As far as I see, you don't really care about District column, you only look after Region, right?
Set NullInterpret = 'Null';
LOAD 'ALL' as Region,'ALL' as District, Value,
If(len(trim(Region))=0, 'ALL',Region) as NewRegion;
LOAD * INLINE [
Region District Value
Null Null 19
Null Null 5
Null Null 8
Null Null 11
Region 1 Null 1
Region 2 Null 5
Region 3 District 1 10
] (delimiter is '\t');
Sorry my mistake,
When Region is Null then in new column show value 'ALL', when region is not null and district is null then show value of 'REGION COLUMN'
Replace region and district column with value 'ALL'
Region District
Region 1 Null
Region 2 Null
Null Null
Null Null
Null Null
Output should be
Region District New Column
All All Region 1
All All Region 2
All All All
All All All
All All All
What if both Region and District are not Null?
Then in new column the value should be Territory.