Need to pick the last row in TrxTime

[#1]

Hi Guys,

I required a data in sql to export in qvd.

Below is example of the audit trial table.

There is data which is all column the data is the same, accept TrxDate and TrxTime.

As such the last row is latest information need to pickup.

Please assist me about the script.

CompanyCode	BranchCode	LinkId	TransactionNo	ContainerKey	TrxDate	TrxTime	ModuleId	ActCode
MF	PC	OUB247187	OUB247187	OUB247187-001	06-05-2014 00:00	06-05-2014 07:08	3	TP200
MF	PC	OUB247187	OUB247187	OUB247187-001	06-05-2014 00:00	06-05-2014 07:35	3	TP200
MF	PC	OUB247187	OUB247187	OUB247187-001	06-05-2014 00:00	06-05-2014 07:35	3	TP200
MF	PC	OUB247187	OUB247187	OUB247187-001	06-02-2014 00:00	06-02-2014 07:35	3	TP200

TQ.

[cgdonders]

Hi,

You can use the peek() function: peek(fieldname [ , row [ , tablename ] ] )

e.g. Peek(‘Employee Count’, 0)  loads the first row. Using the minus sign references from the last row up. e.g. Peek(‘Employee Count’, -1)  loads the last row. If no row is specified, the last row (-1) is assumed. 

[cgdonders]

more information on peek() function: Peek() vs Previous() – When to Use Each

[its_anandrjs]

Hi,

You have to use Peek( ),Previous( ) and RowNo( ) functions to get the desire row loading.

Thanks & Regards

[maxgro]

S:

load

  *,

  rowno() as Id,

  CompanyCode & '-' &      BranchCode & '-' &      LinkId  & '-' &     

  TransactionNo  & '-' &      ContainerKey  & '-' &     ModuleId  & '-' &      ActCode  as NewField;

LOAD CompanyCode,                                                                 // replace with your db read

     BranchCode,

     LinkId,

     TransactionNo,

     ContainerKey,

     TrxDate,

     TrxTime,

     ModuleId,

     ActCode

FROM

[http://community.qlik.com/thread/111278]

(html, codepage is 1252, embedded labels, table is @1);

T:

NoConcatenate

load

  *

  //if(NewField<>peek(NewField),1,0) as FlagLast                    // if you only want add flag uncomment here, comment where clause 

Resident S

where NewField<>peek(NewField)                                  // get only first record by date desc and time desc in group NewField

order by NewField, TrxDate desc, TrxTime desc;

DROP Table S;

[its_anandrjs]

Hi,

You have to load your script some thing like below script

Temp:

load Date#(TrxDate,'D/M/YYYY hh:mm') AS TrxDate, CompanyCode,BranchCode,LinkId,TransactionNo,ContainerKey,Date#(TrxTime,'D/M/YYYY hh:mm') as TrxTime,ModuleId,ActCode;

load * Inline

[

CompanyCode,BranchCode,LinkId,TransactionNo,ContainerKey,TrxDate,TrxTime,ModuleId,ActCode

MF, PC, OUB247187, OUB247187, OUB247187-001,6/5/2014 00:00,6/5/2014 7:08,3,TP200

MF, PC, OUB247187, OUB247187, OUB247187-001,6/5/2014 00:00,6/5/2014 7:35,3,TP200

MF, PC, OUB247187, OUB247187, OUB247187-001,6/5/2014 00:00,6/5/2014 7:35,3,TP200

MF, PC, OUB247187, OUB247187, OUB247187-001,6/2/2014 00:00,6/2/2014 7:35,3,TP200

];

Temp2:

LOAD

CompanyCode,

BranchCode,

LinkId,

TransactionNo,

ContainerKey,

TrxDate,

TrxTime,

ModuleId,

ActCode,

RowNo() as Rowid

Resident Temp;

DROP Table Temp;

Temp3:

LOAD *,

if(Previous(TrxDate) = (TrxDate),0,Peek(Rowid)+1) as Flag

Resident Temp2;

DROP Table Temp2;

NoConcatenate

Data:

LOAD

CompanyCode,BranchCode,LinkId,TransactionNo,ContainerKey,TrxDate,TrxTime,ModuleId,ActCode

Resident Temp3 Where Flag > 0;

DROP Table Temp3;

Hope this helps

Thanks & Regards

[#7]

Hi Coen Donders,

Thank you for the reply.

If I using Peek() or Previous() instead of 4 row it shown 3 row, the row whic is 100% same will be remove.

My needs is the last row in TrxTime which is the latest row 06-02-2014 07:35 should shown.

CompanyCode	BranchCode	LinkId	TransactionNo	ContainerKey	TrxDate	TrxTime	ModuleId	ActCode
MF	PC	OUB247187	OUB247187	OUB247187-001	06-05-2014 00:00	06-05-2014 07:08	3	TP200
MF	PC	OUB247187	OUB247187	OUB247187-001	06-05-2014 00:00	06-05-2014 07:35	3	TP200
MF	PC	OUB247187	OUB247187	OUB247187-001	06-02-2014 00:00	06-02-2014 07:35	3	TP200

[#8]

How to use RowNo()

[#9]

Hi Anand,

Thank for the reply.

After go through and test with script given and the result as below

TEMP3:

MF, PC, OUB247187, OUB247187, OUB247187-001,6/5/2014 00:00,6/5/2014 7:08,3,TP200,2301

MF, PC, OUB247187, OUB247187, OUB247187-001,6/5/2014 00:00,6/5/2014 7:35,3,TP200,2302

MF, PC, OUB247187, OUB247187, OUB247187-001,6/5/2014 00:00,6/5/2014 7:35,3,TP200,0

MF, PC, OUB247187, OUB247187, OUB247187-001,6/2/2014 00:00,6/2/2014 7:35,3,TP200,2304

DATA:

MF, PC, OUB247187, OUB247187, OUB247187-001,6/5/2014 00:00,6/5/2014 7:35,3,TP200,0

It supposedly shown the last row because show which is correct.

MF, PC, OUB247187, OUB247187, OUB247187-001,6/2/2014 00:00,6/2/2014 7:35,3,TP200

[jagan]

Hi,

Use

LOAD DISTINCT

*

FROM DataSource;

DISTINCT will remove duplicate records.

Regards,

Jagan.