I already tried to use some kind of Join statement but it ends up in a stack overflow message.
Also Nicole's solution, it statically only looks for two times occurence. But I want it a bit more flexible, e.g. I also want only records that occur four times, six times, ...
To be honest it should also consider the Country in its selection, but I think that shouldn't be any different to a two field solution. So, the table looks like this:
Id Description Reference Id Company Country 1 This A QlikTech UK 2 description B QlikTech UK 3 might A QlikTech IT 4 be B IKEA UK 5 different B IKEA UK 6 in A QlikTech IT 7 every A IKEA IT 8 record B QlikTech UK 9 but A IKEA IT 10 that A QlikTech UK 11 does B IKEA UK 12 not A QlikTech IT 13 matter A QlikTech IT
Now it should only load records of
UK - IKEA - B
IT - QlikTech - A
I also want to add some code snippet I used
Count("Linked Id") as No_of_occurence
group by Country, Company, "Linked Id";
Where No_of_occurence > MaxOccur; //MaxOccur = 2
Left Join (LedgerEntry)
using your example table you would get what you need with this:
[Reference Id]&'|'&Company&'|'&Country as Key
load Key, count(Key) as Occurences resident First_run group by Key;
NoConcatenate load * Resident First_run where Occurences>2;
drop table First_run;
You could make a loader app of the first table, save the table to a qvd file and load it with different qv app. Kind of splitting the tasks to a different qvw files. It should decrease the memory need. 3.1 million rows still isn't that much - but if the task manager shows that you are using plenty of memory, then the insufficient memory could be the reason for the error.