topic Re: Tagged and Untagged in QlikView

Tagged and Untagged

kulasekhar — Mon, 25 May 2015 10:37:32 GMT

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

Re: Tagged and Untagged

sujeetsingh — Mon, 25 May 2015 10:42:51 GMT

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;

Re: Tagged and Untagged

kulasekhar — Mon, 25 May 2015 11:01:41 GMT

I didn't undastand

Re: Tagged and Untagged

PrashantSangle — Mon, 25 May 2015 11:10:10 GMT

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

Re: Tagged and Untagged

kulasekhar — Mon, 25 May 2015 11:19:54 GMT

doesn't work

Re: Tagged and Untagged

avinashelite — Mon, 25 May 2015 11:23:06 GMT

try with exist and not exist functions

Re: Tagged and Untagged

kulasekhar — Mon, 25 May 2015 11:51:21 GMT

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

Re: Tagged and Untagged

PrashantSangle — Tue, 26 May 2015 04:44:35 GMT

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

Re: Tagged and Untagged

timanshu — Tue, 26 May 2015 06:22:49 GMT

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;