topic Add Recent Date Flag in QlikView

Add Recent Date Flag

Mon, 17 Nov 2014 03:48:23 GMT

Hi All,

I have a table containing the three values:

Transaction Timestamp,

New Value,

Old Value.

I load them as shown:

LOAD ID,

[Old Value] ,

[New Value] ,

[Transaction Timestamp],

Timestamp#([Transaction Timestamp],'mm/dd/yyyy hh:mm:ss') as 'Formatted Timestamp'

FROM

My Table;

What I would like is to have a flag in the load script to identify the most recent transaction. Can someone please help me here? Do I need a self join with group by or is there a way I can check the value and add a flag in the same table? I am struggling with the syntax here as the timestamp field also need to be formatted ( as shown above)

Thanks,

Praveen

Re: Add Recent Date Flag

sujeetsingh — Mon, 17 Nov 2014 04:07:30 GMT

Table2:

LOAD ID,

[Old Value] ,

[New Value] ,

[Transaction Timestamp],

Timestamp#([Transaction Timestamp],'mm/dd/yyyy hh:mm:ss') as 'Formatted Timestamp'

interval(Today()-[Transaction Timestamp],'dd') as Recent

FROM

My Table;

FinalData:

Load ID

,min(Recent)

resident Table2:

drop table Table2;

Re: Add Recent Date Flag

SergeyMak — Mon, 17 Nov 2014 04:07:44 GMT

Try to add this script

LEFT JOIN (YourTableName)

LOAD

ID,

Max([Transaction Timestamp]) AS [Transaction Timestamp],

1 AS Last

Resident YourTableName

GROUP BY ID;

Regards,

Sergey

Re: Add Recent Date Flag

jagan — Mon, 17 Nov 2014 05:02:25 GMT

Hi,

Try like this

Data:

LOAD ID,

[Old Value] ,

[New Value] ,

[Transaction Timestamp],

Timestamp#([Transaction Timestamp],'mm/dd/yyyy hh:mm:ss') as 'Formatted Timestamp'

FROM

My Table;

LEFT JOIN(Data)

LOAD

ID,

Max(Formatted Timestamp) as 'Formatted Timestamp',

1 AS MaxTimeStampFlag

RESIDENT Data

GROUP BY ID;

Hope this helps you.

Regards,

Jagan.