topic Re: requirement in QlikView

requirement

Anonymous — Fri, 05 Jan 2018 11:26:46 GMT

i have two qvds file,

with ids as common field,

so now I want to get the is which are in qvd1 and not in qvd2 and vice versa,

how to do that

Re: requirement

neha_shirsath — Fri, 05 Jan 2018 11:57:14 GMT

For id which are in qvd1 but not in qvd2-

Load qvd1 and

left join

load qvd2;

For id exist in qvd2 but not in qvd1-

Load qvd2 and

left join

load qvd1;

Re: requirement

Anonymous — Fri, 05 Jan 2018 12:15:06 GMT

ok thanks,

so I will get the data by above ,so for getting the values as its has around 5million ids,how shud I export as its taking lot of time to get the data ,

actually I want to know what all ids exists in qvd 1 and not in qvd2 and vice versa,

is there any other way?

Re: requirement

Anil_Babu_Samineni — Fri, 05 Jan 2018 13:00:14 GMT

Either you need Key to generate all common fields to one and the final table you must required to use call that Key into Exist() function?

Re: requirement

Anonymous — Fri, 05 Jan 2018 13:17:48 GMT

and how to do that?

Re: requirement

Anil_Babu_Samineni — Fri, 05 Jan 2018 13:19:02 GMT

Can we know more insight of your data and result set?

Re: requirement

Anonymous — Fri, 05 Jan 2018 13:36:20 GMT

so I have around 5 million records in one qvd and 2 million records in other qvd I need to get the uncommon ids

which exists in frst qvd and not second and vice versa

Re: requirement

petter — Fri, 05 Jan 2018 13:41:23 GMT

Here is a script that will give you an example of how to determine the IDs:

QVD1:
LOAD
  Ceil( Rand()*50 ) AS ids1,
  1 AS F1
AUTOGENERATE 20;


QVD2:
LOAD
  Ceil( Rand()*50 ) AS ids2,
  2 AS F2
AUTOGENERATE 20;


NOT_IN_QVD1:
LOAD
  ids1 as ids_not_in2
RESIDENT
  QVD1
WHERE
  Not(Exists(ids2,ids1));


NOT_IN_QVD2:
LOAD
  ids2 as ids_not_in1
RESIDENT
  QVD2
WHERE
  Not(Exists(ids1,ids2));


COMMON:
LOAD
  ids1 as common_ids
RESIDENT
  QVD1
WHERE
  Exists(ids2,ids1);

Re: requirement

Anonymous — Mon, 08 Jan 2018 07:05:44 GMT

what I did is:

load qvd1

load qvd2(where not exists(id))

but its giving me wrong numbers,

is it a right approach?

Re: requirement

satheshreddy — Mon, 08 Jan 2018 07:08:49 GMT

Hi,

for this you should try not exits function and load you can get it.

Regards

Sathesh

Re: requirement

Anonymous — Mon, 08 Jan 2018 07:36:30 GMT

For id which are in qvd1 but not in qvd2-

Load qvd1 and

left join

load qvd2;

if I take a count of ids after loading above ,

it will give me the count of common ids in two table den I need to subtract it from total count?