topic Re: Latest Notes on Account in App Development

Latest Notes on Account

Wed, 02 Aug 2017 18:07:58 GMT

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

Re: Latest Notes on Account

sunny_talwar — Wed, 02 Aug 2017 18:38:01 GMT

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

rwunderlich — Wed, 02 Aug 2017 18:42:41 GMT

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

Thu, 03 Aug 2017 04:02:18 GMT

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?

Re: Latest Notes on Account

sunny_talwar — Thu, 03 Aug 2017 15:02:22 GMT

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?

Re: Latest Notes on Account

Thu, 03 Aug 2017 15:10:14 GMT

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