Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
Please Have a look into this https://community.qlik.com/docs/DOC-3412
Vikas
Could please upload a sample qvw?
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 |
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;
How you are getting region1 value 3 for final table as you are picking null region?
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
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"
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?
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: