topic Re: Previous day max record in QlikView

Previous day max record

Anonymous — Fri, 10 Jul 2015 07:20:51 GMT

Hi All,

Find the below table and am looking for the solution

DW_LOAD_DATE, NUM,COUNTER

7/8/2015 11:10:20 AM,1,1

7/8/2015 12:05:11 AM,1,1

7/9/2015 01:15:11 PM,2,3

7/9/2015 02:15:11 PM,2,3

7/10/2015 01:15:11 PM,2,3

From above DW_LAOD_DATE how to fetch only highligheted record.Basically the max time stamp on proevious day.

Kindly help on this

-Jay

Re: Previous day max record

Gysbert_Wassenaar — Fri, 10 Jul 2015 07:43:49 GMT

See attached example.

Re: Previous day max record

swuehl — Fri, 10 Jul 2015 07:45:17 GMT

=Timestamp(max({<DW_LOAD_DATE = {">=$(=daystart(today()-1))<=$(=dayend(today()-1))"}>} DW_LOAD_DATE))

Re: Previous day max record

Anonymous — Fri, 10 Jul 2015 07:49:16 GMT

Hi Wassenaar,

Thank you for reply. Actually I need to filter out that particular record in WHERE condition and it will be single record

in the application. Let me know if you need more info

All I want is previous day record

Re: Previous day max record

Anonymous — Fri, 10 Jul 2015 07:50:43 GMT

HI Swuhel,

Thank you for your reply, I need to filter out yesterday's record in script level. I guess I was not clear while posting

-Jay

Re: Previous day max record

PrashantSangle — Fri, 10 Jul 2015 07:59:05 GMT

Hi,

try

where FLOOR(DW_LOAD_DATE)=FLOOR(TODAY()-1)

Regards

Re: Previous day max record

swuehl — Fri, 10 Jul 2015 08:02:15 GMT

Maybe like

Set TimeStampFormat = 'M/D/YYYY hh:mm:ss TT';

INPUT:

LOAD * INLINE [

DW_LOAD_DATE, NUM,COUNTER

7/8/2015 11:10:20 AM,1,1

7/8/2015 12:05:11 AM,1,1

7/9/2015 01:15:11 PM,2,3

7/9/2015 02:15:11 PM,2,3

7/10/2015 01:15:11 PM,2,3

];

RESULT:

FIRST (1)

NOCONCATENATE

LOAD * RESIDENT INPUT

WHERE DW_LOAD_DATE >= Daystart(Today(1)-1) and DW_LOAD_DATE <= Dayend(Today(1)-1)

ORDER BY DW_LOAD_DATE DESC;

DROP TABLE INPUT;

Re: Previous day max record

Gysbert_Wassenaar — Fri, 10 Jul 2015 08:13:00 GMT

Perhaps like this:

Temp:

LOAD Max(DW_LOAD_DATE) AS MAX

FROM ...source...

WHERE DW_LOAD_DATE < DayStart(Today());

LET vMax = peek('MAX', 'Temp');

DROP TABLE Temp;

Result:

LOAD * FROM ...source...

WHERE DW_LOAD_DATE = $(vMax);

Re: Previous day max record

jagan — Fri, 10 Jul 2015 08:17:02 GMT

Hi,

Try this script

Data:

LOAD

Date(Floor(Date#(DW_LOAD_DATE, 'M/D/YYYY hh:mm:ss TT'))) AS Date,

TimeStamp(Timestamp#(DW_LOAD_DATE, 'M/D/YYYY hh:mm:ss TT')) AS DW_LOAD_DATE,

NUM, COUNTER

INLINE [

DW_LOAD_DATE, NUM,COUNTER

7/8/2015 11:10:20 AM,1,1

7/8/2015 12:05:11 AM,1,1

7/9/2015 01:15:11 PM,2,3

7/9/2015 02:15:11 PM,2,3

7/10/2015 01:15:11 PM,2,3];

INNER JOIN(Data)

LOAD

Date,

Timestamp(Max(DW_LOAD_DATE), 'M/D/YYYY hh:mm:ss TT') AS DW_LOAD_DATE

RESIDENT Data

WHERE Date = Date(Today() - 1)

Group By Date;

Regards,

Jagan.