Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
AWS Degraded - You may experience Community slowness, timeouts, or trouble accessing: LATEST HERE
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

problem with date fields

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_NameRecord_count,Processed_date
A.QVW

10000

01/03/2018
B.QVW2000001/03/2018
C.QVW3000001/03/2018
A.QVW900001/02/2018
B.QVW1800001/02/2018
C.QVW2700001/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_NameCurrent Refresh dateCurrent Refresh CountPrior Refresh datePrior Refresh Count
A.QVW01/03/20181000001/02/20189000
B.QVW01/03/20182000001/02/201818000
C.QVW01/03/20183000001/02/201827000

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.

19 Replies
Anonymous
Not applicable
Author

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
Not applicable
Author

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
Not applicable
Author

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
Not applicable
Author

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

sunny_talwar

ok

Anonymous
Not applicable
Author

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