Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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;
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';
load
.....
where not [Value] = 'EXIT' ;
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.
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;
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;
Thanks a ton Manish Kachhia and Massimo Grossi