topic Re: exclude rows while loading in QlikView

exclude rows while loading

Thu, 17 Jul 2014 14:36:24 GMT

I have data something like below table 'Info'

Info:

ItemNo Date Value

10 6/27/2014 AA

10 6/28/2014 BB

10 6/29/2014 CC

20 6/29/2014 BB

30 7/1/2014 AA

10 7/1/2014 EXIT

20 7/1/2014 CC

20 7/2/2014 DD

30 7/3/2014 EXIT

40 7/3/2014 BB

While loading the data from 'Info' table, I don't want to load all those rows of item no. for which 'Value' has reached to EXIT

Result:

ItemNo Date Value

20 6/29/2014 BB

20 7/1/2014 CC

20 7/2/2014 DD

40 7/3/2014 BB

Please help.

Re: exclude rows while loading

richnorris — Thu, 17 Jul 2014 14:39:19 GMT

Pretty sure you just want to do

Info:

LOAD * FROM sourceqvd.qvd (qvd) where Value = 'EXIT';

EDIT:

Oh, sorry you want the opposite of that? So you'll need to do:

Info:

LOAD * FROM sourceqvd.qvd (qvd) where not Value = 'EXIT';

Re: exclude rows while loading

Anonymous — Thu, 17 Jul 2014 14:39:22 GMT

load

.....

where not [Value] = 'EXIT' ;

Re: exclude rows while loading

Thu, 17 Jul 2014 14:50:12 GMT

No..
That will exclude only two rows that contains Value = 'EXIT', I want to exclude all rows for ItemNo 10 and 30.

It's like, I have items that goes to different phases (value) of production, once the production reached to build (Exit)
I want to remove all its records from the table.

Hope it make sense.

Re: exclude rows while loading

MK_QSL — Thu, 17 Jul 2014 14:52:25 GMT

Temp:

Load * Inline

[

ItemNo, Date, Value

10, 6/27/2014, AA

10, 6/28/2014, BB

10, 6/29/2014, CC

20, 6/29/2014, BB

30, 7/1/2014, AA

10, 7/1/2014, EXIT

20, 7/1/2014, CC

20, 7/2/2014, DD

30, 7/3/2014 , EXIT

40, 7/3/2014, BB

];

Temp2:

Load ItemNo as TempItemNo Resident Temp Where Value = 'EXIT';

NoConcatenate

FINAL:

Load * Resident Temp Where Not Exists (TempItemNo, ItemNo);

Drop Table Temp;

Drop Table Temp2;

Re: exclude rows while loading

maxgro — Thu, 17 Jul 2014 14:56:25 GMT

load * inline [

ItemNo, Date , Value

10 , 6/27/2014 , AA

10 , 6/28/2014 , BB

10 , 6/29/2014 , CC

20 , 6/29/2014 , BB

30 , 7/1/2014 , AA

10 , 7/1/2014 , EXIT

20 , 7/1/2014 , CC

20 , 7/2/2014 , DD

30 , 7/3/2014 , EXIT

40 , 7/3/2014 , BB

];

exclude:

load distinct ItemNo as ItemToExclude resident t where Value = 'EXIT';

final:

NoConcatenate load * resident t

where not exists(ItemToExclude, ItemNo);

drop table t;

Re: exclude rows while loading

Thu, 17 Jul 2014 15:21:03 GMT

Thanks a ton Manish Kachhia and Massimo Grossi