topic Re: match records and create flag in QlikView

match records and create flag

Wed, 19 Mar 2014 10:09:48 GMT

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.

Re: match records and create flag

its_anandrjs — Wed, 19 Mar 2014 10:23:07 GMT

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

Re: match records and create flag

MK_QSL — Wed, 19 Mar 2014 10:30:06 GMT

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

Re: match records and create flag

Wed, 19 Mar 2014 10:49:40 GMT

Its working perfect.

Thanks

Vinay

Re: match records and create flag

MK_QSL — Wed, 19 Mar 2014 10:55:29 GMT

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;

Re: match records and create flag

er_mohit — Wed, 19 Mar 2014 10:57:35 GMT

see the attached file

Re: match records and create flag

its_anandrjs — Wed, 19 Mar 2014 11:02:16 GMT

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