Join using condition

[#1]

I want to join two tables and get the output as per below:

Input Table:

Region	District	Terr	Value
Region 1	Null	Null	1
Region 2	District 1	Terr 1	2
Null	Null	Null	3
Region 3	District 1	Null	4

If region is equal to null then pick that row and give and output as per below:

Region 1	All	Null	3
Region 2	All	Null	3
Region 3	All	Null	3

[vikasmahajan]

Please Have a look into this https://community.qlik.com/docs/DOC-3412

Vikas

[senpradip007]

Could please upload a sample qvw?

[#4]

Would not be able to share?

I want to join two tables and get the output as per below:

Input Table - Table 1:

Region	District	Terr	Value
Region 1	Null	Null	1
Region 2	District 1	Terr 1	2
Null	Null	Null	3
Region 3	District 1	Null	4

Join

Input Table - Table 2:

Region	District	Terr	Value
Region 1	Null	Null	1
Region 2	District 1	Terr 1	2
Null	Null	Null	3
Region 3	District 1	Null	4

Output - Create new table with value If region is equal to null then pick that row and give output as per below:

Region 1	All	Null	3
Region 2	All	Null	3
Region 3	All	Null	3

[pho3nix90]

Without knowing more about what data it is, and a proper explanation of what you are exactly trying to achieve, I will assume that if there are null regions, they need to be assigned all regions, and districts also needs to be made 'All'. But still difficult since both data set examples are exactly the same.

I would try something like the following, if this does not solve your issue then you will need to provide more in depth explanation of what you require.

Input:
NoConcatenate LOAD * INLINE [
    Region, District, Terr, Value
    Region 1,,, 1
    Region 2, District 1, Terr 1, 2
    ,,, 3
    Region 3, District 1,, 4
];


Input2:
NoConcatenate LOAD * INLINE [
    Region, District, Terr, Value
    Region 1,,, 1
    Region 2, District 1, Terr 1, 2
    ,,, 3
    Region 3, District 1, , 4
];




NullVals: NoConcatenate LOAD * Resident Input Where not Len(Trim(Region)) > 0 ;
Regions: NoConcatenate LOAD Distinct Region Resident Input2 Where Len(Trim(Region)) > 0 ;
DROP Table Input, Input2;
LET a = 0;


DO WHILE a < NoOfRows('Regions')
  NewVals:
  LOAD Peek('Region',$(a),'Regions') as Region, 'All' as District, Terr, Value, '' as nocon Resident NullVals;
  LET a = $(a)+1;
LOOP


DROP Table NullVals;

[Kushal_Chawda]

How you are getting region1 value 3 for final table as you are picking null region?

[PrashantSangle]

Hi,

try below logic modify as per requirement.

table1:

LOAD * INLINE [

Region, District, Terr, Value

Region 1, Null, Null, 1

Region 2, District 1, Terr 1, 2

Null, Null, Null, 3

Region 3, District 1, Null, 4

];

//table2:

Join

LOAD Value as NewValue

Resident table1 where Region = 'Null';

//exit SCRIPT;

table2:

NoConcatenate

LOAD Region as R1,District as D1,Terr as T1,NewValue as Value1

Resident table1 where not wildmatch(Region,'Null');

Drop table table1;

Regards

[#8]

I tried the above script provided by you, but it's not working.

Yes, your assumption is correct "I will assume that if there are null regions, they need to be assigned all regions, and districts also needs to be made 'All"

[pho3nix90]

Could you upload a reduced qvw? Or sample data of the actual data? I will be able to assist better if I know what the actual data looks like.

EDIT: Are the 2 tables in the actual script 2 different tables? or are you actually using the exact same data to determine the region?

[pho3nix90]

Here is an updated script.

This will accounts for true null's, null strings and empty strings

Input:  
NoConcatenate LOAD * INLINE [  
    Region, District, Terr, Value  
    Region 1,,, 1  
    Region 2, District 1, Terr 1, 2  
    ,,, 3  
    Region 3, District 1,, 4  
];  


Input2:  
NoConcatenate LOAD * INLINE [  
    Region, District, Terr, Value  
    Region 1,,, 1  
    Region 2, District 1, Terr 1, 2  
    ,,, 3  
    Region 3, District 1, , 4  
];  




NullVals: NoConcatenate LOAD * Resident Input Where Len(Trim(Region)) = 0 or IsNull(Region) or WildMatch(Region,'null');  
Regions: NoConcatenate LOAD Distinct Region Resident Input2 Where Len(Trim(Region));  
DROP Table Input, Input2;  
LET a = 0;  


DO WHILE a < NoOfRows('Regions')  
  NewVals:  
  LOAD Peek('Region',$(a),'Regions') as Region, 'All' as District, Terr, Value, '' as nocon Resident NullVals;  
  LET a = $(a)+1;  
LOOP  


DROP Table NullVals;  
DROP Field nocon;

Obviously change the table names with the correct ones. On line 23, 29, 24, 25 and Line 31

Here is the test case: