Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Not applicable

Latest Notes on Account

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

1 Solution

Accepted Solutions

Re: Latest Notes on Account

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"

;

5 Replies
MVP
MVP

Re: Latest Notes on Account

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

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"

;

Not applicable

Re: Latest Notes on Account

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?

MVP
MVP

Re: Latest Notes on Account

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?

Not applicable

Re: Latest Notes on Account

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 NameNote ContentCreated Time
BBSSSWon06/20/2015
RRSLost04/15/2017