Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

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

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

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

Partner
Partner

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

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
Contributor

Re: LOAD just distinct fields from one QVD and store into another 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;

Not applicable

Re: LOAD just distinct fields from one QVD and store into another 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;

almamy_diaby
Contributor

Re: LOAD just distinct fields from one QVD and store into another 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;

MVP
MVP

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

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

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

Thanks for the reply.

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

almamy_diaby
Contributor

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

Try  this

FULL:

load distinct

CASE_NO as KEY,

Field2,

---- ,

FieldN

FROM

[LOCATION\FULL.qvd]

(qvd);

store * from  FULL  into   LOCATION\Distinct.qvd ;

shekharnil
New Contributor III

Re: LOAD just distinct fields from one QVD and store into another 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 ;