topic Re: Re: Date match in QlikView

Date match

Fri, 25 Jul 2014 08:00:04 GMT

HI Qlikers,

Please find the attachment.

please take two seperate fields from two sheets(sheet1 -> orderdate, sheet 2-> executiondate)

i have two fields order date and executiondate

when ever i select any order date , then particular month and that particular range has to be highlighted

for eg:

if i select 1-7-2014 then in executiondate 1-10 dates(Available executiondates) has to be highlighted.

if i select 12-7-2014 then in executiondate 12-20 dates(Availabe executiondates) has to be highlighted

if i select 24-7-2014 then in executiondate 24-monthend dates(Availabe executiondates) has to be highlighted

Please give me some script code for this.

Thanks & Regards

Sampath Botla

Re: Date match

Raj11 — Fri, 25 Jul 2014 09:08:12 GMT

Hi,

try this code:

Data:

LOAD OrderDate,
ExecutionDate
FROM
treeview.xlsx
(ooxml, embedded labels, table is Sheet1);

tempoderDate:
Load
Distinct
OrderDate
Resident
Data;

Let vOrderDateCount =

NoOfRows('tempoderDate');

for

i = 0

$(vOrderDateCount)

step 1

Let

vOrderDate =

Peek('OrderDate',

$(i),'tempoderDate');

Let

vdiff =

if(

Floor((

Day(

Date('$(vOrderDate)','MM-DD-YYYY'))-1)/10) = 2,

day(

MonthEnd(

Date('$(vOrderDate)','MM-DD-YYYY'))) - (

Day(

Date('$(vOrderDate)','MM-DD-YYYY'))),(

Floor((

Day(

Date('$(vOrderDate)','MM-DD-YYYY'))-1)/10) + 1) * 10 -(

Day(

Date('$(vOrderDate)','MM-DD-YYYY'))));

Load

OrderDate,

ExecutionDate

New_ExecutionDate

Resident
Data

Where

ExecutionDate >= '$(vOrderDate)'

And

ExecutionDate<=

OrderDate + '$(vdiff)'
;

NEXT

Re: Date match

amit_saini — Fri, 25 Jul 2014 09:14:30 GMT

Sampath,

This may help you:

Calculating day’s b/w dates:

Syntax: LOAD OpenDate,

CloseDate,

CloseDate-OpenDate AS DaysOpen,

Floor((CloseDate-OpenDate)/10)*10 & ' to ' & (Floor((CloseDate-OpenDate)/10)*10+9) AS Range

Output:

OpenDate	CloseDate	DaysOpen	Range
28/02/2012	02/03/2012	3	0 to 9
07/03/2012	15/03/2012	8	0 to 9
01/01/2012	10/01/2012	9	0 to 9
14/01/2012	24/01/2012	10	10 to 19
11/03/2012	24/03/2012	13	10 to 19
28/04/2012	12/05/2012	14	10 to 19
27/01/2012	11/02/2012	15	10 to 19
08/02/2012	23/02/2012	15	10 to 19
07/01/2012	24/01/2012	17	10 to 19
23/04/2012	11/05/2012	18	10 to 19
30/01/2012	19/02/2012	20	20 to 29
12/03/2012	01/04/2012	20	20 to 29
08/02/2012	29/02/2012	21	20 to 29
09/04/2012	01/05/2012	22	20 to 29
17/02/2012	11/03/2012	23	20 to 29
08/04/2012	03/05/2012	25	20 to 29
24/02/2012	07/04/2012	43	40 to 49

Re: Re: Date match

Raj11 — Fri, 25 Jul 2014 09:28:46 GMT

Check this file