Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All.
tab1:
load * Inline [
LeadKey,Value
1,1
2,2
3,3
4,4
5,5
6,6
7,7
];
tab2:
LOAD * Inline [
LeadKey,Value2
1,1
2,2
3,3
4,4
8,8
9,9
10,10
11,11
12,12
13,13
];
I want the out put is Tagged=1,2,3,4(only commen field)
Untagged= 8,9,10,11,12,13
You can use full outer join in qlik script and generate a flag as
join(Table1)
LeadKey
,LeadKey as LeadKey_2
resident Table2;
Drop Table2;
Final Data:
Load
LeadKey
,if(LeadKey=LeadKey_2,LeadKey) as Tagged
,if(LeadKey<>LeadKey_2,LeadKey) as Untagged
Resident Table1;
drop table Table1;
I didn't undastand
Hi,
try below
Tab1:
load * Inline [
LeadKey,Value
1,1
2,2
3,3
4,4
5,5
6,6
7,7
];
Tab2:
LOAD * Inline [
LeadKey,Value2
1,1
2,2
3,3
4,4
8,8
9,9
10,10
11,11
12,12
13,13
];
NoConcatenate
Final_Table:
Load * Resident Tab1;
Join
Load LeadKey as LeadKey1,
Value2
Resident Tab2;
Drop tables Tab1,Tab2;
Final_Data:
Load
LeadKey,
if(LeadKey=LeadKey1,LeadKey) as Tagged,
if(LeadKey<>LeadKey1,LeadKey) as Untagged,
Value,
Value2
Resident Final_Table;
drop table Final_Table;
Regards
doesn't work
try with exist and not exist functions
Hi All.
tab1:
load * Inline [
LeadKey,Value
1,1
2,2
3,3
4,4
5,5
6,6
7,7
];
tab2:
LOAD * Inline [
LeadKey,Value2
1,1
2,2
3,3
4,4
8,8
9,9
10,10
11,11
12,12
13,13
];
I want the out put is Tagged=1,2,3,4(only commen field)
Untagged= 8,9,10,11,12,13
Thanks
sekhar
Hi,
What about 5,6,7 from tab1.
It comes under which Flag..
Find below solution
Tab1:
load * Inline [
LeadKey,Value
1,1
2,2
3,3
4,4
5,5
6,6
7,7
];
NoConcatenate
Tab2:
LOAD * Inline [
LeadKey,Value2
1,1
2,2
3,3
4,4
8,8
9,9
10,10
11,11
12,12
13,13
];
NoConcatenate
Final_Table:
Load *,LeadKey as LeadKey1 Resident Tab1;
JOIN
Load LeadKey,
LeadKey AS LeadKey2,
Value2
Resident Tab2
WHERE Exists(LeadKey,LeadKey);
Drop tables Tab1,Tab2;
Final_Data:
Load
if(LeadKey1=LeadKey2,LeadKey) as Tagged,
if(LeadKey1<>LeadKey2,LeadKey) as Untagged, //If you want 5,6,7,8,9,10,11,12,13 as Untagged
if(LeadKey1<>LeadKey2,LeadKey2) as Untagged1, //If you want 8,9,10,11,12,13 as Untagged
Value,
Value2
Resident Final_Table;
drop table Final_Table;
Regards
Hi,
Just copy this :
tab1:
load * Inline [
LeadKey,Value
1,1
2,2
3,3
4,4
5,5
6,6
7,7
];
tab2:
LOAD * Inline [
LeadKey2,Value2
1,1
2,2
3,3
4,4
8,8
9,9
10,10
11,11
12,12
13,13
];
tab22:
Load *, 'Untagged' as Flag resident tab2 where not Exists(LeadKey,LeadKey2) ;
Concatenate(tab22)
Load *, 'tagged' as Flag resident tab2 where Exists(LeadKey,LeadKey2);
right join(tab2)
Load * Resident tab2 ;
drop table tab2;
tab11:
Load *,'Untagged' as Flag resident tab1 where not Exists(LeadKey2,LeadKey);
Concatenate(tab11)
Load *, 'tagged' as Flag resident tab1 where Exists(LeadKey2,LeadKey);
right join(tab1)
Load * Resident tab1;
drop table tab1;
//-------------If you want flag in one table ------------>
Final :
NoConcatenate
Load LeadKey as LeadKeyNew,Value as ValueNew,Flag as FlagNew Resident tab11 order by LeadKey;
join
load LeadKey2 as LeadKeyNew,Value2 as ValueNew,Flag as FlagNew Resident tab22
;
rename table tab11 to tab1;
rename table tab22 to tab2;