topic Re: problem with date fields in QlikView

problem with date fields

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: problem with date fields

sunny_talwar — Wed, 03 Jan 2018 16:46:09 GMT

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

Re: problem with date fields

sunny_talwar — Wed, 03 Jan 2018 16:49:26 GMT

Check this out

Re: problem with date fields

Anonymous — Wed, 03 Jan 2018 18:46:01 GMT

Hi Sunny

Thanks alot...

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

regards

Prasanth

Re: problem with date fields

Anonymous — Wed, 03 Jan 2018 18:48:08 GMT

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.

Re: problem with date fields

sunny_talwar — Wed, 03 Jan 2018 19:01:22 GMT

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

];

Re: problem with date fields

Anonymous — Thu, 04 Jan 2018 09:50:53 GMT

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.

Re: problem with date fields

sunny_talwar — Thu, 04 Jan 2018 09:53:56 GMT

Just use red() in the background color expression

Re: problem with date fields

devarasu07 — Thu, 04 Jan 2018 09:59:47 GMT

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())

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

Re: problem with date fields

Anonymous — Tue, 09 Jan 2018 07:52:29 GMT

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.

Re: problem with date fields

Anonymous — Tue, 09 Jan 2018 07:52:56 GMT

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.

Re: problem with date fields

sunny_talwar — Tue, 09 Jan 2018 09:22:14 GMT

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;

Re: problem with date fields

Anonymous — Tue, 09 Jan 2018 12:26:24 GMT

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

Re: problem with date fields

sunny_talwar — Tue, 09 Jan 2018 12:29:10 GMT

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

Re: problem with date fields

Anonymous — Tue, 09 Jan 2018 12:59:19 GMT

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

Re: problem with date fields

sunny_talwar — Tue, 09 Jan 2018 13:01:04 GMT

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

Re: problem with date fields

Anonymous — Tue, 09 Jan 2018 13:15:08 GMT

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

Re: problem with date fields

sunny_talwar — Tue, 09 Jan 2018 13:25:45 GMT

Re: problem with date fields

Anonymous — Tue, 09 Jan 2018 15:00:54 GMT

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.

Re: problem with date fields

sunny_talwar — Tue, 09 Jan 2018 15:03:44 GMT

No problem at all