Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
haymarketpaul
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

1 Solution

Accepted Solutions
Anil_Babu_Samineni

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

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

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
Anil_Babu_Samineni

May be one front-end solution is this?

Capture.PNG

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

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)
neelamsaroha157
Specialist II
Specialist II

Check this out.

marcus_sommer

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

neelamsaroha157
Specialist II
Specialist II

Forgot to add the inner join.

Anil_Babu_Samineni

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

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

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)
haymarketpaul
Creator III
Creator III
Author

Perfect - Thank You