7 Replies Latest reply: Nov 26, 2013 8:05 AM by Janne Miettinen RSS

    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?

        • Re: Load only records that occur several times
          Nicole Smith

          See the load script in the attached.

          • Re: Load only records that occur several times
            Srikanth P

            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;

              • 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

                  • 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 *;

                    • Re: Load only records that occur several times
                      Janne Miettinen

                      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