Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Load only records that occur several times

Hi community,

could anybody give me a hint on how to load only records that occur several times? Assume I have the following table:

IdDescriptionReferenced Id
1ThatA
2doesB
3notA
4matterC
5atA
6allB

Now I want QlikView to load only those records that have a Referenced Id that occurs more than two times. In that case it should only load records of Referenced Id A as it appears in three records.

I already tried to use mapping load and it works pretty fine for this simple example. But it doesn't work if the table definition gets more complex as mapping load only allows me to use two columns. E.g. when I also want to separate between different companies:

IdDescriptionReferenced IdCompany
1ThisAQlikTech
2isBQlikTech
3justAGoogle
4aAQlikTech
5textAGoogle
6thatBQlikTech
7doesAQlikTech
8notBGoogle
9matterAGoogle

In this case QlikView should only load records of Referenced Id A as we have three A's for QlikTech and three A's for Google. Referenced Id B should be ignored as we only have two for QlikTech and one for Google.

Any idea?

7 Replies

Re: Load only records that occur several times

See the load script in the attached.

Not applicable

Re: Load only records that occur several times

Simply join the tables on that fields. Please find the below sample script:

T:

LOAD Reference_id , Company , Count(Reference_id & '-'Company) AS CNT

FROM soure

group by Reference_id , Company;

MAIN:

LOAD Reference_id, Company Resident T where cnt > 2;

Left Join (MAIN)

Load * from source;

Drop table T;

Not applicable

Re: Re: Load only records that occur several times

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:

IdDescriptionReference IdCompanyCountry
1ThisAQlikTechUK
2descriptionBQlikTechUK
3mightAQlikTechIT
4beBIKEAUK
5differentBIKEAUK
6inAQlikTechIT
7everyAIKEAIT
8recordBQlikTechUK
9butAIKEAIT
10thatAQlikTechUK
11doesBIKEAUK
12notAQlikTechIT
13matterAQlikTechIT

Now it should only load records of

UK - IKEA - B

IT - QlikTech - A

Not applicable

Re: Re: Re: Load only records that occur several times

I also want to add some code snippet I used

No_of_occurence_TMP:

LOAD

    "Linked Id",

    Company,

    Country,

    Count("Linked Id") as No_of_occurence

Resident LedgerEntry_TMP

group by Country, Company, "Linked Id";

QUALIFY *;

LedgerEntry:

LOAD *

Resident No_of_occurence_TMP

Where No_of_occurence > MaxOccur; //MaxOccur = 2

Left Join (LedgerEntry)

LOAD

    Id,

    "Linked Id",

    "Posting Date",

    "Cost Centre",

    "Account No",

    "Source Code",

    "Document No",

    Description

Resident LedgerEntry_TMP;

UNQUALIFY *;

mr_janne
New Contributor III

Re: Load only records that occur several times

Hi,

using your example table you would get what you need with this:

First_run:

LOAD
Id,
Description,
[Reference Id],
Company,

Country,
[Reference Id]&'|'&Company&'|'&Country as Key
FROM
Example_Table;


left join(First_run)

load Key, count(Key) as Occurences resident First_run group by Key;

Result_table:
NoConcatenate load * Resident First_run where Occurences>2;

drop table First_run;


regards.Janne

Not applicable

Re: Load only records that occur several times

Yes, I also think that should work. But when I do this I get an out of memory exception. I use QV 10 SR5 on a Windows XP machine with 2gb ram and I have about 3.1 million records in my table. Sounds like not undersized, does it?

mr_janne
New Contributor III

Re: Load only records that occur several times

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.

Community Browser