topic HI, all in QlikView

HI, all

ravindraa — Wed, 07 May 2014 07:25:45 GMT

we have two systems in those systems there might be all values common values but some case not same.

Databse1: It maintain ContracID and MembershipID

Database2: it is also maintain ContracID and MembershipID as well as some fields.

but our requierment we need to Display The common record and as well as uncommon records.how can it is possible.

this is very urgent could you give any suggesiion.

Re: HI, all

kaushiknsolanki — Wed, 07 May 2014 07:30:49 GMT

Hi,

Try this.

Connect to Database1.

DATA:

Load ContracID ,MembershipID from xyztable;

Connect to Database2.

Concatenate (DATA)

Load ContracID ,MembershipID from pqrtable;

FINAL_DATA:

load distinct ContracID, MembershipID as MEMBERSHIP_ID resident DATA;

Drop table DATA;

Here final table will have data from both the tables.

Regards,

Kaushik Solanki

Re: HI, all

anbu1984 — Wed, 07 May 2014 07:33:10 GMT

Common records

Table1:

Sql Select ContracID & MembershipID as key From Table

Table2:

Sql Select ContracID, MembershipID From Table

Where Exists(ContracID & MembershipID,key);

Use Where Not Exists(ContracID & MembershipID,key) to get non matching records

Re: HI, all

ravindraa — Wed, 07 May 2014 08:15:14 GMT

Dear

Kaushik Solanki,

Thanks for given value answer using this we can get the all data (common and uncommon records)

here we should show the how many common records is existed

and how many uncommon records existed.

I mean common records count:

uncommon records count:

please give me any suggestion.

Re: HI, all

kaushiknsolanki — Wed, 07 May 2014 08:20:20 GMT

Hi,

You can just get the count and know which whether the data is common or not. If count is more then 1 then its common and if its not then its uncommon.

Something like this.

Connect to Database1.

DATA:

Load ContracID ,MembershipID from xyztable;

Connect to Database2.

Concatenate (DATA)

Load ContracID ,MembershipID from pqrtable;

FINAL_DATA:

load ContracID, MembershipID as MEMBERSHIP_ID,count(ContactID) as COUNT resident DATA group by

ContracID, MembershipID;

Drop table DATA;

Regards,

Kaushik Solanki

Re: HI, all

anbu1984 — Wed, 07 May 2014 08:22:03 GMT

Use Where Exists to get common records and Where Not exist to uncommon records. check my previous reply below

Re: HI, all

its_anandrjs — Wed, 07 May 2014 08:28:10 GMT

You can add a Flag field like below script as the Kaushik suggest but you already get the Count of the records

DATA:

Load ContracID ,MembershipID from xyztable;

Concatenate (DATA)

Load ContracID ,MembershipID from pqrtable;

FINAL_DATA_Temp:

Load ContracID, MembershipID as MEMBERSHIP_ID,count(ContactID) as COUNT

Resident DATA group by ContracID, MembershipID;

Drop table DATA;

==========

Final:

Load ContracID, MembershipID as MEMBERSHIP_ID, COUNT,

IF(COUNT >= 2,'Common Record','UnCommon Record') as Rec_Details

Resident FINAL_DATA_Temp;

Drop FINAL_DATA_Temp;

Re: HI, all

ravindraa — Wed, 07 May 2014 08:39:27 GMT

Dear Anbu,

Thanks for given suggestion i have checked that code it is not matching with my requirement could you give if any other suggestion.

after get the both data how can i divided common data count

uncommon data count .

Re: HI, all

ravindraa — Wed, 07 May 2014 08:56:43 GMT

Dear

Kaushik Solanki,

After you sent above logic i am finding which is common data and uncommon records,

we need to show per ex: database1 have 10,000 records.

database 2 have 10,200 records

in this case we need to show 10,000 as a common records

200 as a uncommon records i mean if there is any unmatched to each other.

If you have any suggestion please share.

Re: HI, all

Wed, 07 May 2014 09:25:30 GMT

Table1:

LOAD * INLINE [

ContracID ,MembershipID

1,1

1,2

1,3

2,1

2,3

3,2

3,3

];

Concatenate(Table1)

LOAD * INLINE [

ContracID ,MembershipID

1,1

1,3

2,1

2,4

2,3

3,3

4,5

4,3

];

Table2:

LOAD

ContracID ,MembershipID,if(Sum(1)=2,'Y','N') AS Flag

Resident Table1

Group By ContracID ,MembershipID

;

DROP TABLE Table1;

Then on chart, you can group by Flag, sum(1) to get matched and unmatched number.

Re: HI, all

ravindraa — Thu, 08 May 2014 04:04:03 GMT

Wenkun Ma,

Using above logic i am getting how many records in 1 database and

how many records in 2 data base

we want how many records common records existed from both and

how many records uncommon records existed from both.

Please give any suggestion.

Re: HI, all

ravindraa — Thu, 08 May 2014 04:22:42 GMT

Dear

Kaushik Solanki,

After you sent above logic i am finding which is common data and uncommon records,

we need to show per ex: database1 have 10,000 records.

database 2 have 10,200 records

in this case we need to show 10,000 as a common records

200 as a uncommon records i mean if there is any unmatched to each other.

If you have any suggession please share.

so per we are getting how many record in one data base as well as other data base,

here we need to write how many records common in both and how many records in uncommon

if(flag a=b ),count(contract))

you have any suggession please share.