Concatenate two data sources using condition

[#1]

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"

[swuehl]

Could you post two small sample input tables and your expected result?

[#3]

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

[Kushal_Chawda]

try this

LOAD *,

         if(len(trim(Region))=0 and len(trim(District))=0 ,'All',

         if(not isnull(Region),Region)) as NewColumn

...

FROM atble

[swuehl]

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

[#6]

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

[swuehl]

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');

[#8]

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

[swuehl]

What if both Region and District are not Null?

[#10]

Then in new column the value should be Territory.