Script mentioned below brings only the data that has the combination > 1. I hope you can take it forward from here to avoid data duplication using the timestamp or anything specific to your requirements. Does it help?
LOAD DISTINCT MemberID, CustomerID,
COUNT(MemberID&'_'&CustomerID) AS Count
GROUP BY MemberID, CustomerID;
LOAD MemberID&'_'&CustomerID AS Key
WHERE Count >1;
DROP TABLE Temp;
WHERE EXISTS (Key,MemberID&'_'&CustomerID);
DROP TABLE Key;
take a look at attached qvw...
LOAD * INLINE [
ID, max(worth) as worth
RESIDENT TMP GROUP by ID;
DROP TABLE TMP;
I think Group by clause just works with data from resident table.
Hope it helps.
- PS -
test.qvw 130.0 K
the script is pasted in my last post...?!
With variables you mean fields(columns)? With more fields you get an error I think.
I can imagine 2 possible ways to get the data for a full table(perhaps someone knows a better solution):
1. Do as described an then do a join with the origin table to get the rest of the fields.But I'm not that confident with joins so I would do it like this:
2. In short: Create a flag field in the origin table which fields should be loaded(load = 1 or 0)
Create a merged field of ID and worth, like "ID|worth" while loading TEST(and origin table) and create a Mapping table with that data like:
ID|worth,1 as relevant
Than load data(fields) from origin table(TMP) with:
ApplyMap('RELEVANT_ID_MAP',ID|worth,'0') AS load,
And finally load the data in a table with ... RESIDENT sometemptable where load=1;
Look at it more as rough guide not a step by step tutorial...Sorry, I have not the time at the moment. Perhaps later I'll create a sample.
I admit it looks a bit complicated but if you are used to it is really quick and for me it the safest way. Better than joining and not 100% sure what happens to my data, ending up in a mess.
And finally I think there is sure somebody (more advanced) who can do this "better"/easier. But at the moment this works for me pretty well.
- PS -