topic Re: Concatenate two data sources using condition in QlikView

Concatenate two data sources using condition

Wed, 25 Nov 2020 16:16:04 GMT

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"

Re: Concatenate two data sources using condition

swuehl — Tue, 07 Jun 2016 08:58:43 GMT

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

Re: Concatenate two data sources using condition

Tue, 07 Jun 2016 09:27:11 GMT

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

Re: Concatenate two data sources using condition

Kushal_Chawda — Tue, 07 Jun 2016 09:33:53 GMT

try this

LOAD *,

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

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

...

FROM atble

Re: Concatenate two data sources using condition

swuehl — Tue, 07 Jun 2016 09:35:51 GMT

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

Re: Concatenate two data sources using condition

Tue, 07 Jun 2016 09:45:22 GMT

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

Output should be

Region District New Column

All All Region 1

All All Region 2

All All All

Re: Concatenate two data sources using condition

swuehl — Tue, 07 Jun 2016 09:47:44 GMT

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

Re: Concatenate two data sources using condition

Tue, 07 Jun 2016 09:53:58 GMT

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

Output should be

Region District New Column

All All Region 1

All All Region 2

All All All

Re: Concatenate two data sources using condition

swuehl — Tue, 07 Jun 2016 09:58:14 GMT

What if both Region and District are not Null?

Re: Concatenate two data sources using condition

Tue, 07 Jun 2016 10:03:19 GMT

Then in new column the value should be Territory.

Re: Concatenate two data sources using condition

swuehl — Tue, 07 Jun 2016 10:07:17 GMT

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.

Re: Concatenate two data sources using condition

Tue, 07 Jun 2016 10:12:24 GMT

How can I embed the above code in below one:

LOAD Region,

District,

Value

FROM

;

Re: Concatenate two data sources using condition

swuehl — Tue, 07 Jun 2016 10:18:15 GMT

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

Re: Concatenate two data sources using condition

Tue, 07 Jun 2016 10:21:57 GMT

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

Re: Concatenate two data sources using condition

Thu, 09 Jun 2016 09:47:49 GMT

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

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

Re: Concatenate two data sources using condition

Thu, 09 Jun 2016 09:48:56 GMT

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

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

Re: Concatenate two data sources using condition

swuehl — Thu, 09 Jun 2016 10:14:06 GMT

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

Re: Concatenate two data sources using condition

Thu, 09 Jun 2016 10:34:10 GMT

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

;

Re: Concatenate two data sources using condition

Thu, 09 Jun 2016 10:37:27 GMT

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

Re: Concatenate two data sources using condition

swuehl — Thu, 09 Jun 2016 10:41:32 GMT

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