5 Replies Latest reply: Aug 3, 2017 11:10 AM by Bernard Lemnyuy RSS

    Latest Notes on Account

    Bernard Lemnyuy

      Hi,

          I am very new to Qlik and I am trying to make sense of a model I am trying to create.

      I want to get the last note entered under a certain  accounts.

       

      Consider the following tables:

       

      Accounts table

      Account NameAccount ID
      RRS64657
      BBSSS12389

       

      Notes Table

       

      Note IDAccount IDNote contentCreated Time
      9646
      64657
      Discovery03/01/2016
      9876
      64657
      Lost04/15/2017
      8621
      64657
      demo11/04/2016
      543612389Discovery04/10/2015
      589412389Won06/20/2015

       

      From these tables, I want to be able to get the following results

       

      Account NameNote ContentCreated Time
      RRSLost04/15/2017
      BBSSSWon06/20/2015

       

      So I am looking for the latest time a note was entered into the account. If I am able to do this at the data load editor it will be best. Any help will be appreciated.

      Thank you

      Ben

        • Re: Latest Notes on Account
          Sunny Talwar

          May be this

           

          AccTable:

          LOAD * INLINE [

              Account Name, Account ID

              RRS, 64657

              BBSSS, 12389

          ];

           

          Left Join (AccTable)

          LOAD [Account ID],

          FirstSortedValue([Note content], -[Created Time]) as [Note content],

          Date(Max([Created Time])) as [Created Time]

          Group By [Account ID];

          LOAD * INLINE [

              Note ID, Account ID, Note content, Created Time

              9646, 64657, Discovery, 03/01/2016

              9876, 64657, Lost, 04/15/2017

              8621, 64657, demo, 11/04/2016

              5436, 12389, Discovery, 04/10/2015

              5894, 12389, Won, 06/20/2015

          ];

          • Re: Latest Notes on Account
            Rob Wunderlich

            Sunny's is good. Here's another approach.

             

            Accounts:

            LOAD

                "Account Name",

                "Account ID"

            FROM [lib://thread]

            (html, utf8, embedded labels, table is @1);

             

             

            Notes:

            LOAD

                "Note ID",

                "Account ID",

                "Note content",

                "Created Time"

            FROM [lib://thread]

            (html, utf8, embedded labels, table is @6)

            ;

             

             

            INNER JOIN(Notes)

            LOAD

                "Account ID",

                max("Created Time") as "Created Time"

            Resident Notes

            Group by "Account ID"

            ;

              • Re: Latest Notes on Account
                Bernard Lemnyuy

                Thank you Sunny and Rob. It works perfectly.  I tried to use the logic for 3 tables,

                architect.png

                Accounts:

                LOAD

                    AccountName,

                    "Account ID"

                FROM [lib://Qliktest (qlik-dev_dev)/Accounts.xlsx]

                (ooxml, embedded labels, table is Sheet1);

                 

                 

                Notes:

                LOAD

                    NOTEID,

                    "Note Content",

                    "Created Time"

                FROM [lib://Qliktest (qlik-dev_dev)/Notes.xlsx]

                (ooxml, embedded labels, table is Sheet1);

                 

                 

                Hub:

                LOAD

                    NOTEID,

                    "Account ID"

                FROM [lib://Qliktest (qlik-dev_dev)/Hub.xlsx]

                (ooxml, embedded labels, table is Sheet1);

                 

                 

                 

                 

                Noconcatenate

                Accounttemp:

                load

                AccountName,

                "Account ID"

                resident Accounts;

                 

                 

                inner join(Accounttmp) load NOTEID resident Hub;

                 

                 

                Inner join(Hub)

                Load

                NOTEID,

                max("Created Time") as lastnote

                resident Notes

                Group By NOTEID;

                drop table Accounttemp,

                 

                 

                It does not seem to give me the latest date. it gets both dates. What Am I doing wrong?