Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator III
Creator III

Load and consolidate latest populated field

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

EmailEventIDJobTitleIndustryPAC#
1@me.com2JobTitle2Media11844
1@me.com3Medicine
1@me.com1JobTitle1
1@me.com4Banking52278

so i would end up with...

EmailEventIDJobTitleIndustryPAC#
1@me.com4JobTitle2Banking52278

Any ideas how best to accomplish this much appreciated

Tags (1)
1 Solution

Accepted Solutions
Highlighted

Re: Load and consolidate latest populated field

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);

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)

View solution in original post

6 Replies
Highlighted

Re: Load and consolidate latest populated field

May be one front-end solution is this?

Capture.PNG

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Highlighted
Specialist II
Specialist II

Re: Load and consolidate latest populated field

Check this out.

Highlighted
MVP & Luminary
MVP & Luminary

Re: Load and consolidate latest populated field

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

Highlighted
Specialist II
Specialist II

Re: Load and consolidate latest populated field

Forgot to add the inner join.

Highlighted

Re: Load and consolidate latest populated field

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);

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)

View solution in original post

Highlighted
Creator III
Creator III

Re: Load and consolidate latest populated field

Perfect - Thank You