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

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

Fri, 31 Jan 2014 11:59:55 GMT

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

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

Fri, 31 Jan 2014 12:10:22 GMT

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

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

jvishnuram — Fri, 31 Jan 2014 12:14:47 GMT

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;

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

almamy_diaby — Fri, 31 Jan 2014 12:18:19 GMT

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;

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

Fri, 31 Jan 2014 12:18:35 GMT

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;

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

almamy_diaby — Fri, 31 Jan 2014 12:24:11 GMT

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;

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

jonathandienst — Fri, 31 Jan 2014 12:39:48 GMT

Try this:

LOAD CASE_NO,

...

FROM [LOCATION\FULL.qvd] (qvd)

WHERE NOT EXISTS(CASE_NO);

STORE ....;

HTH

Jonathan

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

Fri, 31 Jan 2014 12:45:51 GMT

Thanks for the reply.

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

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

almamy_diaby — Fri, 31 Jan 2014 14:52:06 GMT

Try this

FULL:

load distinct

CASE_NO as KEY,

Field2,

---- ,

FieldN

FROM

[LOCATION\FULL.qvd]

(qvd);

store * from FULL into LOCATION\Distinct.qvd ;

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

Anonymous — Mon, 03 Feb 2014 08:49:44 GMT

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 ;

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

Mon, 03 Feb 2014 13:54:03 GMT

Hi Ali, Please make sure that DISTINCT_KEY table is not concatenated to another Table. I believe you dont need 2 steps. Try Like below:

DISTINCT_KEY:

Noconcatenate

LOAD DISTINCT CASE_NO AS KEY FROM SOURCE.qvd (qvd) ;

STORE DISTINCT_KEY INTO DISTINCT.qvd (qvd) ;

DROP Table DISTINCT_KEY;

(or)

DISTINCT_KEY:

Noconcatenate

LOAD CASE_NO AS KEY FROM SOURCE.qvd (qvd) WHERE NOT EXISTS(CASE_NO);

STORE DISTINCT_KEY INTO DISTINCT.qvd (qvd) ;

DROP Table DISTINCT_KEY;

Please reload the qvw and check the data in qvd.

If this is not working, may be you are doing something wrong and post the actual scipt from qvw.