Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Guys,
I have a massive QVD which has around 200 Million records in it. I need to extract just the distinct key from this qvd. The key is just one field called CASE_NO. I've tried the following;
Lets call the original QVD PAYMENT. I first loaded just the keys from this qvd into a table called FULL
FULL:
load
CASE_NO as CASE
FROM
[LOCATION\FULL.qvd]
(qvd);
DISTINCT_KEY:
load
Distinct CASE_NO as KEY
resident FULL;
store DISTINCT_KEY into LOCATION\Distinct.qvd;
The above works but for some reason Distinct.qvd contains ALL keys from the FULL.qvd instead of just the distinct unique ones.
Any help will be appreciated!
Thanks
Hi Ali
In DISTINCT_KEY you're trying to load CASE_NO column from FULL, but in FULL it's named CASE - is this example working?
BTW, make sure you're not concatenating DISTINCT_KEY to FULL
Lukasz
Hi Ali,
You are taking a resident so the field you are referring should be the alias name of the current field.,
i.e.,
FULL:
load
CASE_NO as CASE
FROM
[LOCATION\FULL.qvd]
(qvd);
DISTINCT_KEY:
load
Distinct CASE as KEY
resident FULL;
store DISTINCT_KEY into LOCATION\Distinct.qvd;
FULL:
load
CASE_NO as CASE
FROM
[LOCATION\FULL.qvd]
(qvd);
noconcatenate
DISTINCT_KEY:
load Distinct
CASE_NO as KEY
resident FULL;
Drop table FULL;
store DISTINCT_KEY into LOCATION\Distinct.qvd;
Sorry i made a mistake while typing out the code i used. I do use the ALIAS in DISTINCT_KEY.
the code I use is
FULL:
load
CASE_NO as CASE
FROM
[LOCATION\FULL.qvd]
(qvd);
DISTINCT_KEY:
load
Distinct CASE as KEY
resident FULL;
store DISTINCT_KEY into LOCATION\Distinct.qvd;
FULL:
load
CASE_NO as CASE
FROM
[LOCATION\FULL.qvd]
(qvd);
noconcatenate
DISTINCT_KEY:
load Distinct
CASE as KEY
resident FULL;
drop table FULL;
store DISTINCT_KEY into LOCATION\Distinct.qvd;
Hi
Try this:
LOAD CASE_NO,
...
FROM [LOCATION\FULL.qvd] (qvd)
WHERE NOT EXISTS(CASE_NO);
STORE ....;
HTH
Jonathan
Thanks for the reply.
This didn't work. It still stored everything, not the distinct values
Try this
FULL:
load distinct
CASE_NO as KEY,
Field2,
---- ,
FieldN
FROM
[LOCATION\FULL.qvd]
(qvd);
store * from FULL into LOCATION\Distinct.qvd ;
Why do you want a 2 step solution.
You can select distinct directly when you are loading from QVD.
i.e.,
FULL:
load
Distinct CASE_NO as CASE
FROM
[LOCATION\FULL.qvd]
(qvd);
store * from FULL into LOCATION\Distinct.qvd ;