topic Re: reduce items in QlikView

reduce items

gidon500 — Fri, 02 May 2014 07:04:25 GMT

I have inventory file , includes pn and qty

as well as list of pn to exclude fro the invery

what is the best way to do it

enclosed a file with data

sheet - data , with original data .

sheet - exclude - pn to be removed fro the original data

sheet - after - required data ( after the remove )

thanks

gidon

Re: reduce items

nizamsha — Fri, 02 May 2014 07:10:53 GMT

=sum({$<pn-={"105","106"}>}qty)

Re: reduce items

nizamsha — Fri, 02 May 2014 07:11:40 GMT

pn as dimension

expression =sum({$<pn-={"105","106"}>}qty)

Re: reduce items

its_anandrjs — Fri, 02 May 2014 07:15:16 GMT

New Update

Write for load statement like below if you have few records otherwise use where exists

LOAD pn,

qty

FROM

(ooxml, embedded labels, table is data)

Where not Match(pn,'105','107');

In Front end for expression

=Sum({$<pn -= {'105','106'}>} qty )

Re: reduce items

gidon500 — Fri, 02 May 2014 07:16:38 GMT

Hi Nizam

Thanks for the fast answer

I cannot use this script , as the list is much bigger .

and it change every day

thanks gidon

Re: reduce items

Fri, 02 May 2014 07:23:46 GMT

hi david i think you can do anand has said ,i think it is correct

Re: reduce items

anbu1984 — Fri, 02 May 2014 07:28:42 GMT

Data:

LOAD pn,

qty

FROM

[Reduce items.xlsx]

(ooxml, embedded labels);

Left Join(Data)

Exclude:

LOAD pn, RowNo() As Rn

FROM

[Reduce items.xlsx]

(ooxml, embedded labels, table is exclude);

Final:

NoConcatenate

Load pn,qty,Rn Resident Data where IsNull(Rn);

Drop table Data;

Re: reduce items

gidon500 — Fri, 02 May 2014 07:28:47 GMT

i cannot be specific with the pn , it changed

and the list is very long

thanks

any right or keep command ?

gidon

Re: reduce items

anbu1984 — Fri, 02 May 2014 07:30:48 GMT

Check my script below

Re: reduce items

its_anandrjs — Fri, 02 May 2014 07:31:17 GMT

You can write this way in the load script

ExcludeTable:

LOAD pn as ExcludeKey;

LOAD pn

FROM

(ooxml, embedded labels, table is exclude);

LOAD pn,

qty

FROM

(ooxml, embedded labels, table is data) Where not Exists(ExcludeKey,pn);

Re: reduce items

gidon500 — Fri, 02 May 2014 07:34:47 GMT

thanks , it works

gidon

Re: reduce items

its_anandrjs — Fri, 02 May 2014 07:41:11 GMT

Check below script david in this you have to maintain only excel by use this you can exclude 100 to 1000 of pn field keys.

ExcludeTable:

LOAD pn as ExcludeKey;

LOAD pn

FROM

(ooxml, embedded labels, table is exclude);

LOAD pn,

qty

FROM

(ooxml, embedded labels, table is data) Where not Exists(ExcludeKey,pn);

Drop table ExcludeTable;

Re: reduce items

anbu1984 — Fri, 02 May 2014 07:57:34 GMT

Can you mark helpful\correct answer

Re: reduce items

gidon500 — Fri, 02 May 2014 08:03:21 GMT

Thanks tried it it works perfect ,

since you are here , can you tell me

how to remove a field from a table

in order to reduce tha size of the module

thanks

gidon

Re: reduce items

anbu1984 — Fri, 02 May 2014 08:05:31 GMT

Remove the field which you dont need from load statement

Re: reduce items

its_anandrjs — Fri, 02 May 2014 08:08:50 GMT

After the load statement like

Load Name,Address,Salary,LastName

From Location;

DROP Field LastName; //if you want to remove the LastName from table

//Or

DROP Fields LastName,Name; //if you want to remove the LastName,Namefrom table

Re: reduce items

gidon500 — Fri, 02 May 2014 08:09:46 GMT

yes , please

how do i do it ?

Re: reduce items

gidon500 — Fri, 02 May 2014 08:12:13 GMT

thank you very much .

Re: reduce items

its_anandrjs — Fri, 02 May 2014 08:12:59 GMT

If you got correct answer from the thread so mark the thread as appropriate answer correct / helpful for references or for another question post new thread.

Regards

Re: reduce items

anbu1984 — Fri, 02 May 2014 08:13:57 GMT

LOAD pn,

qty

FROM

[Reduce items.xlsx]

(ooxml, embedded labels);

If you dont want column qty remove it from your load statement

LOAD pn

FROM

[Reduce items.xlsx]

(ooxml, embedded labels);

If you want to remove after loading, then use Drop

Drop field qty;