Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I am trying to remove certain records from my result set based on a certain criteria, but due to how the data is it is becoming difficult.
Example:
Opportunity# | ManufacturerID |
2558 | -1 |
2558 | 588 |
230 | -1 |
In the example above, I would like to remove the record with opportunity# 2558 and ManufacturerID of -1. Simply telling Qlikview to remove any ManufacturerID with -1 won't work because there are Opportunities that have only one record (with a manufacturerID of -1) and we want to keep those.
Basically, any Opportunity that returns more than one record of ManufacturerIDs I would like to remove the -1 records, however if there is only one record for the Opportunity keep that one record.
In addition, this needs to be done via the loadscript because I am storing the data into a .txt file for SQL to grab.
Thoughts?
Solution in the Script:
tab1:
LOAD * INLINE [
Opportunity#, ManufacturerID
2558, -1
2558, 588
230, -1
231, 213
231, 211
];
Left Join(tab1)
LOAD Opportunity#, Count(Opportunity#) As Cnt
Resident tab1
Group By Opportunity#
;
tab2:
NoConcatenate
LOAD *
Resident tab1
Where Cnt=1 OR (Cnt>1 And ManufacturerID<>-1)
;
Drop Table tab1;
Solution in the Script:
tab1:
LOAD * INLINE [
Opportunity#, ManufacturerID
2558, -1
2558, 588
230, -1
231, 213
231, 211
];
Left Join(tab1)
LOAD Opportunity#, Count(Opportunity#) As Cnt
Resident tab1
Group By Opportunity#
;
tab2:
NoConcatenate
LOAD *
Resident tab1
Where Cnt=1 OR (Cnt>1 And ManufacturerID<>-1)
;
Drop Table tab1;
This might help you.
Load *,
Opportunity# as _opp
From Data
Where ManufacturerID <> -1;
Concatenate load *
From Data
Where
Not Exists(Opportunity# , _opp)
And ManufacturerID =-1 ; //not necessary
Drop field _opp;