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.

[sunny_talwar]

Why not do this on the front end of the application rather than doing this in the script?

[sunny_talwar · Accepted solution]

Check this out

[Anonymous]

Hi Sunny

Thanks alot...

if i have any queries i will get back to you..

regards

Prasanth

[Anonymous]

Hi Sunny,

Is there anyway to do in script level if yes please can you brief me about that if you don't mine.

regards

Prashanth.

[sunny_talwar]

Try this in the script

Table:

LOAD Extract_Name,

Date(Max(Processed_date)) as Current_Refresh_Date,

FirstSortedValue(Record_count, -Processed_date) as Current_Refresh_Count,

Date(Max(Processed_date, 2)) as Previous_Refresh_Date,

FirstSortedValue(Record_count, -Processed_date, 2) as Previous_Refresh_Count

Group By Extract_Name;

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

];

[Anonymous]

Hi All,

If my Current_Refresh_Count and Previous_Refresh_Count is Same i need to give Same colour(Red) for those two fields.

i am using Pivot table.

Can you please help me out of this issue.

Thanks in advance

regards

Prashanth.

[sunny_talwar]

Just use red() in the background color expression

[devarasu07]

Hi,

in your pivot table measure -->  add below expression in both measure as background color expression

if( sum(Current_Refresh_Count) =sum(Previous_Refresh_Count) ,Red(),Black())

or

if (Column(1)= Column (2), red(),black())  // here col1 is Current referesh count and 2 is previous

also note, if u want to match data and amount same then apply below expression

if( sum(Current_Refresh_Count) =sum(Previous_Refresh_Count) and Current_Refresh_Date=Previous_Refresh_Date, Red(),Black())

Thanks,

Deva

[Anonymous]

Hi Sunny,

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.