Tagged and Untagged

[kulasekhar]

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

[sujeetsingh]

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;

[kulasekhar]

I didn't undastand

[PrashantSangle]

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

[kulasekhar]

doesn't work

[avinashelite]

try with exist and not exist functions

[kulasekhar]

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

[PrashantSangle]

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

[timanshu]

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;