match records and create flag

[#1]

Hi all,

I have two tables like...

MasterAccount

Account1

Account2

Account3

Account4

Account5

Account6

Account7

Account8

Account9

Account10

Account11

Account12

Account13

Account14

Account15

Account16

Account17

Account18

Account19

Account20

and other table

WebAccount

Account4

Account8

Account11

Account2

Account18

Account19

Account20

in script side I want something below, if account has match then create flag in master table like below.

MasterAccount	Flag
Account1	0
Account2	1
Account3	0
Account4	1
Account5	0
Account6	0
Account7	0
Account8	1
Account9	0
Account10	0
Account11	1
Account12	0
Account13	0
Account14	0
Account15	0
Account16	0
Account17	0
Account18	1
Account19	1
Account20	1

Thanks

Vinay

I FORGOT TO TELL YOU THAT IN MASTER TABLE ACCOUNT ARE REPAETED LIKE WE MIGHT GET MULTIPLE TIME SAME ACCOUNTS.

[its_anandrjs]

Hi,

Load some thing like below

Account:

load * Inline

[

MasterAccount

Account1

Account2

Account3

Account4

Account5

Account6

Account7

Account8

Account9

Account10

Account11

Account12

Account13

Account14

Account15

Account16

Account17

Account18

Account19

Account20

];

Concatenate

WAccount:

LOAD WebAccount as MasterAccount;

load * Inline

[

WebAccount

Account4

Account8

Account11

Account2

Account18

Account19

Account20

];

LOAD *,

if(Count(MasterAccount) > 1 ,1,0) as flag

Resident Account

Group by MasterAccount;

Thanks & Regards

[MK_QSL · Accepted solution]

MA:

Load * Inline

[

MasterAccount

Account1

Account2

Account3

Account4

Account5

Account6

Account7

Account8

Account9

Account10

Account11

Account12

Account13

Account14

Account15

Account16

Account17

Account18

Account19

Account20

];

NoConcatenate

WA:

Load * Inline

[

WebAccount

Account4

Account8

Account11

Account2

Account18

Account19

Account20

];

NoConcatenate

FINAL:

Load *, '1' as Flag

Resident MA

Where Exists (WebAccount,MasterAccount) ;

Load *, '0' as Flag

Resident MA Where NOT Exists (WebAccount,MasterAccount);

Drop Table MA;

====================

Now Create a Straight Table with MasterAccount as Dimension and Flag as an expression

Go to presentation tab and untick Suppress Zero - Values

[#4]

Its working perfect.

Thanks

Vinay

[MK_QSL]

Below is the Better Approach...

MA:

Load * Inline

[

MasterAccount

Account1

Account2

Account3

Account4

Account5

Account6

Account7

Account8

Account9

Account10

Account11

Account12

Account13

Account14

Account15

Account16

Account17

Account18

Account19

Account20

];

NoConcatenate

WA:

Load *, WebAccount as MasterAccount Inline

[

WebAccount

Account4

Account8

Account11

Account2

Account18

Account19

Account20

];

NoConcatenate

FINAL:

Load *, '1' as Flag

Resident MA

Where Exists (WebAccount,MasterAccount) ;

Load *, '0' as Flag

Resident MA Where NOT Exists (WebAccount,MasterAccount);

Drop Table MA;

[er_mohit]

see the attached file

[its_anandrjs]

Hi,

One of the another simple way and in this you keep both the tables MasterAccount & WebAccount sepearate and do calculation in another table

Account:

load * Inline

[

MasterAccount

Account1

Account2

Account3

Account4

Account5

Account6

Account7

Account8

Account9

Account10

Account11

Account12

Account13

Account14

Account15

Account16

Account17

Account18

Account19

Account20

];

NoConcatenate

WebAccount:

load * Inline

[

WebAccount

Account4

Account8

Account11

Account2

Account18

Account19

Account20

];

NoConcatenate

Temp:

LOAD

MasterAccount as MKey Resident Account;

Concatenate

LOAD WebAccount as MKey Resident WebAccount;

NewData:

LOAD *,

if(Count(MKey) > 1 ,1,0) as Flag

Resident Temp

Group by MKey;

DROP Table Temp;

EXIT Script;

Hope this helps

Thanks & Regards