problem with date fields

[Anonymous]

Hi All,

I have script like below

Load  Extract_Name,

          Record_count,

          Processed_date

from table.qvd(qvd);

My table in Table_Viewr like

Extract_Name	Record_count,	Processed_date
A.QVW	10000	01/03/2018
B.QVW	20000	01/03/2018
C.QVW	30000	01/03/2018
A.QVW	9000	01/02/2018
B.QVW	18000	01/02/2018
C.QVW	27000	01/02/2018

My table has multiple records

the table gives the record count for each refresh,

the Processed_Date has today function,.

I want design one report like below.

Extract_Name	Current Refresh date	Current Refresh Count	Prior Refresh date	Prior Refresh Count
A.QVW	01/03/2018	10000	01/02/2018	9000
B.QVW	01/03/2018	20000	01/02/2018	18000
C.QVW	01/03/2018	30000	01/02/2018	27000

I know like how to get current and prior dates like below

If(Processed_date=Today(),Processed_date) as Current Refresh date

If(Processed_date=Today()-1,Processed_date) as Prior Refresh date

But it is not fullfill my above requirement.

I need do to get only 2 records for each QVW (i.e.Current Refresh date and Prior Refresh date)

Thanks in advance

Please help me out of this issue.

Thanks & Regards

Prashanth.

[Anonymous]

Hi ,

I don't want store my historical data in my final application.means today i have only 2 records in future i might get million records.

So i just want get latest 2 records for each QVW.

Could you please help on this

Thanks in Advance

regards

Prashanth.

[sunny_talwar]

May be something like this

Table:

LOAD * INLINE [

    Extract_Name, Record_count, Processed_date

    A.QVW, 10000, 01/03/2018

    B.QVW, 20000, 01/03/2018

    C.QVW, 30000, 01/03/2018

    A.QVW, 9000, 01/02/2018

    B.QVW, 18000, 01/02/2018

    C.QVW, 27000, 01/02/2018

    A.QVW, 9000, 01/01/2018

    B.QVW, 18000, 01/01/2018

    C.QVW, 27000, 01/01/2018

];

FinalTable:

NoConcatenate

LOAD *

Resident Table

Where AutoNumber(RowNo(), Extract_Name) <= 2

Order By Extract_Name, Processed_date desc;

DROP Table Table;

[Anonymous]

Hi Sunny,

Thanks Alot..

For A.QVW  it is giving 01/03/2018 and 01/02/2018

but for B.QVW  it is giving only 01/03/2018

i am considering it as current date and prior date,i need to get prior date for B.QVW.

regards

Prashanth

[sunny_talwar]

Not for me... share what you have and I can check

[Anonymous]

Hi Sunny,

My Source Table is like below:

Temp:

LOAD (Extract Name],

[Application Name],

Source,

[Table Name],

[Field Name],

[Record Count],

[Processed Date]

from Table.qvd(qvd);

Noconcatenate

Load * resident Temp

where Autonumber(RowNo(),[Extract Name]) <=2

order by [Extract Name],[Processed Date] desc;

drop table Temp;

My Source Table will be like this

[Extract Name],[Record Count],[Processed date]

A.QVW,10,01/09/2018

B.QVW,20,01/09/2018

A.QVW,30,01/05/2018

B.QVW,40,01/05/2018

A.QVW,50,01/04/2018

B.QVW,60,01/04/2018

ragards

Prashanth

[sunny_talwar]

Okay... so where is the output which isn't working... You will have to probably share a qvw which shows me that it didn't pick two dates for B.QVW

[Anonymous]

Actually i am working on Real application so i couldn't able to share.

[sunny_talwar]

ok

[Anonymous]

Hi Sunny,

Actually some of the QVWs have morethan one field name so it is not working.

If we do like Where Autonumber(Rowno(),[Field Name]<=2

then it is working

Thanks alot for your help..

regards

Prashanth.

[sunny_talwar]

No problem at all