Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
QV12 SR3
I need to load a single record per email based on the latest EventID.
So below it would be EventID 4 but because there is no JobTitle for EventID 4 i would then want the next highest EventID's JobTitle where a value exists
EventID | JobTitle | Industry | PAC# | |
---|---|---|---|---|
1@me.com | 2 | JobTitle2 | Media | 11844 |
1@me.com | 3 | Medicine | ||
1@me.com | 1 | JobTitle1 | ||
1@me.com | 4 | Banking | 52278 |
so i would end up with...
EventID | JobTitle | Industry | PAC# | |
---|---|---|---|---|
1@me.com | 4 | JobTitle2 | Banking | 52278 |
Any ideas how best to accomplish this much appreciated
Back End Solution may be this?
LOAD Email, Max(EventID) as EventID, MaxString(JobTitle) as JobTitle, FirstSortedValue(Industry, -EventID) as Industry,Max(PAC#) as PAC# Group By Email;
LOAD Email,
EventID,
JobTitle,
Industry,
PAC#
FROM
[https://community.qlik.com/message/1283997]
(html, codepage is 1252, embedded labels, table is @1);
May be one front-end solution is this?
Check this out.
With interrecord-functions like Peek() or Previous() ? you could within a sorted resident-load check the current record against the previous record and take this value if there is no one within the current record - afterwards you could filter it with a inner join load (with Email as the Key and max(EventID) of these data.
- Marcus
Forgot to add the inner join.
Back End Solution may be this?
LOAD Email, Max(EventID) as EventID, MaxString(JobTitle) as JobTitle, FirstSortedValue(Industry, -EventID) as Industry,Max(PAC#) as PAC# Group By Email;
LOAD Email,
EventID,
JobTitle,
Industry,
PAC#
FROM
[https://community.qlik.com/message/1283997]
(html, codepage is 1252, embedded labels, table is @1);
Perfect - Thank You