Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
| Discovery | 03/01/2016 | ||
9876 |
| Lost | 04/15/2017 | ||
8621 |
| 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'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"
;
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
];
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"
;
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?
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?
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 |