Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

LOAD just distinct fields from one QVD and store into another qvd

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

10 Replies
Not applicable
Author

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

jvishnuram
Partner - Creator III
Partner - Creator III

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;

almamy_diaby
Creator
Creator

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;

Not applicable
Author

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;

almamy_diaby
Creator
Creator

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;

jonathandienst
Partner - Champion III
Partner - Champion III

Hi

Try this:

LOAD CASE_NO,

        ...

FROM [LOCATION\FULL.qvd] (qvd)

WHERE NOT EXISTS(CASE_NO);

STORE ....;

HTH

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable
Author

Thanks for the reply.

This didn't work. It still stored everything, not the distinct values

almamy_diaby
Creator
Creator

Try  this

FULL:

load distinct

CASE_NO as KEY,

Field2,

---- ,

FieldN

FROM

[LOCATION\FULL.qvd]

(qvd);

store * from  FULL  into   LOCATION\Distinct.qvd ;

Anonymous
Not applicable
Author

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 ;