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 · Accepted solution]

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.

[#3]

How can I embed the above code in below one:

LOAD Region,

     District,

     Value

FROM

;

[swuehl]

Have you tried just using the same preceding load?

Preceding Load

Note that your Nulls in your input table should be empty values or NULL, not the text string 'Null' (that's why I used the Set NullInterpret as workaorund to read the text in as NULL).

[#5]

Thanks a lot for your help, it worked!!!

[#6]

I need to upgrade the below statement:

If(len(trim(Region))<>0 and len(trim(District))=0, Region,If(Len(Trim(Terr))=0 and Len(Trim(Region))<>0,District,Terr)) as NewCol

To

Region is not equal to null and district is null then copy region to NewCol

Secondly, Region is not equal to null and Terr is null then copy District value to that NewCol

Input Table

Region	District	Terr	Value
Region 1	Null	Null	1
Region 2	District 1	Terr1	2
Null	Null	Null	3
Region 2	District 1	Null	6

Output to be

Region	District	Terr	Value	NewCol
Region 1	Null	Null	1	Region 1
Region 2	District 1	Terr1	2
Null	Null	Null	3
Region 2	District 1	Null	6	Region 2

[#7]

I need to upgrade the below statement:

If(len(trim(Region))<>0 and len(trim(District))=0, Region,If(Len(Trim(Terr))=0 and Len(Trim(Region))<>0,District,Terr)) as NewCol

To

Region is not equal to null and district is null then copy region to NewCol

Secondly, Region is not equal to null and Terr is null then copy District value to that NewCol

Input Table

Region	District	Terr	Value
Region 1	Null	Null	1
Region 2	District 1	Terr1	2
Null	Null	Null	3
Region 2	District 1	Null	6

Output to be

Region	District	Terr	Value	NewCol
Region 1	Null	Null	1	Region 1
Region 2	District 1	Terr1	2
Null	Null	Null	3
Region 2	District 1	Null	6	District 1

[swuehl]

Your expected output is not matching your requirement in the last line

Region is not equal to null and district is null then copy region to NewCol

Secondly, Region is not equal to null and Terr is null then copy District value to that NewCol

[#9]

Just a sample, and also want to check one thing my below condition is giving blank value in NewCol:

LOAD *,

           If(len(trim(Region))=0 , Region) as Newcol

FROM

;

[#10]

I have written below condition for the above requirement but it's not working:

       If(len(trim(Region))<>0 and len(trim(District))=0, Region,If(Len(Trim(District))<>0 and Len(Trim(Terr))=0 and Len(Trim(Region))<>0,District)) as NewCol

[swuehl]

'Not  working' is not a good issue description.

If possible, upload a small sample input file that shows all possible combinations of conditions to check and a table that exactely shows your expected result.

A sample output that is not matching your written requirements is not helping much (is the expected output incorrect? or the written requirements?).

The input file is needed to check the range of possible field values and NULL handling issues.