topic Re: Join using condition in QlikView

Join using condition

Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Join using condition

vikasmahajan — Thu, 09 Jun 2016 05:40:39 GMT

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

Vikas

Re: Join using condition

senpradip007 — Thu, 09 Jun 2016 05:43:17 GMT

Could please upload a sample qvw?

Re: Join using condition

Thu, 09 Jun 2016 06:41:31 GMT

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

Re: Join using condition

pho3nix90 — Thu, 09 Jun 2016 09:08:43 GMT

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;

Re: Join using condition

Kushal_Chawda — Thu, 09 Jun 2016 09:11:53 GMT

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

Re: Join using condition

PrashantSangle — Thu, 09 Jun 2016 09:25:36 GMT

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

Re: Join using condition

Thu, 09 Jun 2016 11:02:02 GMT

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"

Re: Join using condition

pho3nix90 — Thu, 09 Jun 2016 11:13:16 GMT

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?

Re: Join using condition

pho3nix90 — Thu, 09 Jun 2016 11:31:39 GMT

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: