Stuck in Problem related group by and Max() - Qlik Community

sarfaraz_sheikh · ‎2015-07-24

Dear All,

I want to calculate highest row based on MODIFIED_DATE with all the associated fields / data

INPUT:

OUTPUT:

LEAD_NO	GENERATED_DATE	MODIFIED_DATE	STATUS_ID
HN01082011000977	17-Jun-15	22-Jul-15	94

How i can acheived the same at Qlikview End;

Sarfaraz

Anonymous · ‎2015-07-24

Try inner join:

INNER JOIN (INPUT) LOAD

date(max(MODIFIED_DATE)) as MODIFIED_DATE

RESIDENT INPUT;

sarfaraz_sheikh · ‎2015-07-24

Dear Michael,

Can you keep example for my understanding ...so that would be eassy for me to draw solution as per expectation..

sarfaraz

Anonymous · ‎2015-07-24

See attched

senpradip007 · ‎2015-07-24

Have look on the attached file.

sasiparupudi1 · ‎2015-07-24

Please try

Data:

load LEAD_NO,GENERATED_DATE,date#(MODIFIED_DATE,'DD-MMM-YY') as MODIFIED_DATE,STATUS_ID

inline [

LEAD_NO,GENERATED_DATE,MODIFIED_DATE,STATUS_ID

HN01082011000977,17-Jun-15,22-Jul-15,94

HN01082011000977,17-Jun-15,21-Jul-15,93

HN01082011000977,17-Jun-15,20-Jul-15,87

HN01082011000977,17-Jun-15,18-Jul-15,75

HN01082011000977,17-Jun-15,15-Jul-15,75

HN01082011000977,17-Jun-15,11-Jul-15,75

HN01082011000977,17-Jun-15,19-Jun-15,74

HN01082011000977,12-Jun-13,17-Jun-15,94

HN01082011000977,17-Jun-15,17-Jun-15,74

HN01082011000977,12-Jun-13,10-Mar-14,94

HN01082011000977,12-Jun-13,24-Jul-13,94

HN01082011000977,12-Jun-13,23-Jul-13,93

HN01082011000977,12-Jun-13,22-Jul-13,92

HN01082011000977,12-Jun-13,12-Jun-13,121

HN01082011000977,01-Aug-11,12-Jun-13,94

];

Inner join

MaxModifiedDate:

LOAD Date(Max(date#(MODIFIED_DATE,'DD-MMM-YY')),'DD-MMM-YY') as MODIFIED_DATE

Resident Data;

Kushal_Chawda · ‎2015-07-24

Create a straight table,

Dimensions - LEAD_NO, GENERATED_DATE, MODIFIED_DATE

Expression =aggr(Max(MODIFIED_DATE),LEAD_NO)

Please see the attached