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

exclude rows while loading

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.

1 Solution

Accepted Solutions
maxgro
MVP
MVP

t:

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;

View solution in original post

6 Replies
richnorris
Creator II
Creator II

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';

Anonymous
Not applicable
Author

load

.....

where not [Value] = 'EXIT' ;

Not applicable
Author

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.

MK_QSL
MVP
MVP

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;

maxgro
MVP
MVP

t:

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;

Not applicable
Author

Thanks a ton Manish Kachhia and Massimo Grossi