Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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?
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?
and how to do that?
Can we know more insight of your data and result set?
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
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);
what I did is:
load qvd1
load qvd2(where not exists(id))
but its giving me wrong numbers,
is it a right approach?
Hi,
for this you should try not exits function and load you can get it.
Regards
Sathesh