Latest Notes on Account

[#1]

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 Name	Account ID
RRS	64657
BBSSS	12389

Notes Table

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

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

Account Name	Note Content	Created Time
RRS	Lost	04/15/2017
BBSSS	Won	06/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

[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

];

[rwunderlich · Accepted solution]

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"

;

[#4]

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

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?

[sunny_talwar]

I am not sure what your final data model needs to look like? Do you wish to have only two tables? Account and Notes? Why do you create Accounttmp and then without using it drop it?

[#6]

Hi Sunny, the table should look like this below. I think that was an error I made with the accounttemp not being used. It was supposed to be on the last inner join.

Account Name	Note Content	Created Time
BBSSS	Won	06/20/2015
RRS	Lost	04/15/2017